SQL server 2005 windows authentication fails and sa password unknown

Question

SQL server 2005 windows authentication fails and sa password unknown

Brian Brown-381342

SSC-Addicted

Points: 401
More actions
November 26, 2008 at 9:03 am

#201766

I'm at a lose here; after running a simple transfer database package in the bus intelligence development suite, the windows authentication now fails with any attempt to connect to the database. I've researched extensively and have found many suggestions to reinstall sql server. A possible solution but would rather not if an alternative exist. The folks that initially installed this server failed to note the sa password or didn't initiate an sa account at all. I've also read something about starting in single user mode, stopping the service and issuing a sql command via sqlcmd window but can't find that page to reference. Any and all help is appreciated and happy Thanksgiving.
Brian

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

Do you have any id avaialable which is sysadmin on this instalation so that you do these single user stuff and issuing a command from command prompt.

MJ

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

Can you not connect to the server at all, or just this database?

Can you connect to master on this instance?

Brian Brown-381342 SSC-Addicted Points: 401 More actions · Answer 3

Thanks for the reply. To my knowledge the accounts that have these rights are domain user accounts which require the windows authentication.

Brian Brown-381342 SSC-Addicted Points: 401 More actions · Answer 4

I am able to login to the server but unable to connect to the master db, same authentication issue.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

MANU-J.

SSC-Dedicated

Points: 31126

November 26, 2008 at 9:28 am

#903875

That means you are not sysadmin there. Right?

MJ

Brian Brown-381342 SSC-Addicted Points: 401 More actions · Answer 6

My ID is part of the administrator group on the server and sql server as well.

Brian Brown-381342 SSC-Addicted Points: 401 More actions · Answer 7

A colleague of mine had the same problem after running the transfer db package but was able to recover as he had the sa account and password.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 8

Then try restarting it in single user mode by following the steps mentioned under this article:

http://blogs.techrepublic.com.com/window-on-windows/?p=390

HTH,

MJ

Brian Brown-381342 SSC-Addicted Points: 401 More actions · Answer 9

Thanks for the tip, I'll give that a try and let you know.

Brian Brown-381342 SSC-Addicted Points: 401 More actions · Answer 10

Follow-up;

I was able to login to the sql server after adding my user ID to this group SQLServer2005MSSQLUser$SERVERNAME$INSTANCENAME and SQLServer2005SQLAgentUser$SERVERNAME$INSTANCENAME.

I still have not figured out why windows authentication does not work with NT AUTHORITY\SYSTEM as part of security\logins.

The access is at least there now with having added the necessary ID'S to these groups.

forsqlserver SSCoach Points: 18902 More actions · Answer 11

Dear MANU

Please tell the location of Rebuildm.exe file.I've searched it in my sql server2005 folders but I've not found it..

Thanx

Thanks

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 12

It's not in SS2K5. You need to use the setup.exe on your installation media.

Not being unfriendly, but you could have googled that and got the answer much faster than you did here. Could save you some time in the future.

Cath

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 13

Find it in the doc attached. Hope you have the backups of system databases with you.

MJ

forsqlserver SSCoach Points: 18902 More actions · Answer 14

forsqlserver

SSCoach

Points: 18902

November 27, 2008 at 9:33 pm

#904543

Dear Manu

Thanks for the DOC.

Thanks