question

ClaudiaK avatar image
ClaudiaK asked

can't logon - windows user accounts re-created instance in Windows Authentication mode

Hi, my SQL instance is running in Windows Authentication mode. The windows accounts that had access to SQL have all been deleted and then recreated on a different server, although the name is the same the password has changed. Consequently (I think), I cannot logon to the db engine or any of the databases, none of the accounts work. I can logon to integration services or analysis services. Does anybody now what I could do, other than salvage the database files and re-install SQL server?
databaseloginauthentication
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
OK, you should still be able to logon using the sa account - its a SQL login. Once you are connected you should then be able to give your DBA's windows group the permission they need to connect to the server. Once thats done, log the sa account off , logon as yourself and reinstate the windows groups/accounts. That should be pretty close to as it was ... The logins on the 'other's server wont have passwords as they are windows accounts. If they are SQL logins that have been transferred then they will have random passwords that the DBAs will be able to assign one by one.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ClaudiaK avatar image ClaudiaK commented ·
Hi Thanx for this, but the instance is not in mixed mode, and as far as I understand this, the sa account can only be enabled if the instance is in mixed mode to begin with.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
0 Likes 0 ·
ClaudiaK avatar image ClaudiaK commented ·
tried this, and no luck, this is what I get: C:\Program Files\Microsoft SQL Server\100\Tools\Binn> sqlcmd -e Msg 18456, Level 14, State 1, Server SERVERNAME, Line 1 Login failed for user 'domain\username'.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Is domainusername in the local windows administrator group when you start up in single user mode? I believe that is required for the workaround in that forum
0 Likes 0 ·
Kevin Feasel avatar image
Kevin Feasel answered
If you can afford the downtime, you could start the server up in single-user mode. At that point, if you are an administrator of the server on which the SQL Server installation resides, you can connect to it and re-grant any access. Here are a couple of links which should be able to help you out: [BOL Link]( http://msdn.microsoft.com/en-us/library/ms188236.aspx) / [Pinal Dave's blog]( http://blog.sqlauthority.com/2009/02/10/sql-server-start-sql-server-instance-in-single-user-mode/).
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ClaudiaK avatar image ClaudiaK commented ·
Thanx, but it won't let me in. I do have the right permissions, is there another reasons why this may fail?
0 Likes 0 ·
Kevin Feasel avatar image Kevin Feasel commented ·
When you [check the event log]( http://sqlserver-qa.net/blogs/tools/archive/2007/05/18/msg-18456-level-14-state-1-server-servername-line-1-login-failed-for-user-name.aspx) in the failure audits, which error state do you get? Also, just to double-check, did you edit the SQL Server service to use the "-m" parameter and then restart it? You should also be able to use -m"Microsoft SQL Server Management Studio - Query" to be able to connect using SSMS in single-user mode [BOL]( http://msdn.microsoft.com/en-us/library/dd207004.aspx).
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.