January 30, 2011 at 8:36 am
I have to reset the sa password . i am an admin on the box. i am not a sa . i dont know sa password. i tried to reset the password in single user mode . it doesnt work. Please help me
January 30, 2011 at 10:01 am
What do you mean 'it doesn't work'?
What exact steps did you follow? What errors did you get?
Do you have a sysadmin account other than sa? A windows authentication one?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2011 at 10:05 am
I don't have any other sysadmin account, i am an administrator on the machine . i am not sysadmin. I don't have any other accounts with sysadmin priviliges.I am trying to do this on sql server 2008 server
January 30, 2011 at 1:22 pm
start the sql server instance from the command prompt in single user mode. Open a second command prompt and connect to the instance using SQLCMD
sqlcmd -Sinstancename
Reset the password using
alter login [sa] with password = 'Newone1'
Exit both command prompts and restart the sql server services as normal
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 30, 2011 at 6:08 pm
i am getting the error user is in single user mode .only one administrator can connect at this time. error 18461.
when i checked the error log i get the following message
2011-01-30 19:56:42.77 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQL2K8R2 ].
2011-01-30 19:56:42.77 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQL2K8R2\sql\query ].
2011-01-30 19:56:42.79 Logon Error: 17187, Severity: 16, State: 1.
2011-01-30 19:56:42.79 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: 2001:0:5ef5:73b8:28a7:2f3d:3f57:fefa]
2011-01-30 19:56:42.99 Logon Error: 17187, Severity: 16, State: 1.
2011-01-30 19:56:42.99 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: 192.168.1.5]
2011-01-30 19:56:43.05 Server Server is listening on [ ::1 <ipv6> 6812].
2011-01-30 19:56:43.51 Server Server is listening on [ 127.0.0.1 <ipv4> 6812].
Thank You
January 30, 2011 at 6:14 pm
when i check the server properties . i can see allow remote connections checked in and remote query timeout 600
January 30, 2011 at 6:23 pm
Are you using SQLCmd or Management Studio? You don't want to use SSMS as it takes multiple connections
January 30, 2011 at 7:29 pm
I used both.First I tried sqlcmd and then I tried ssms
January 30, 2011 at 10:14 pm
Make sure SQL Agent is stopped
Make sure any application that could connect is prevented from doing so
Make sure no one else is trying to connect
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2011 at 10:55 pm
sql server agent is off already. it is not running
January 31, 2011 at 6:06 am
sql2k8 (1/30/2011)
I don't have any other sysadmin account, i am an administrator on the machine . i am not sysadmin. I don't have any other accounts with sysadmin priviliges.I am trying to do this on sql server 2008 server
You are administrator on the box, but you have no administrator account to get into SQL Server, do I understand this correctly?
Do you have any account to be able to login into SQL Server? What permissions in SQL Server do you have?
If you are not a SQL Server Security Admin or SysAdmin, you may not be able to reset the SA password. Box Admin and SQL Server admin are not the same thing.
January 31, 2011 at 6:20 am
Brandie Tarvin (1/31/2011)
Box Admin and SQL Server admin are not the same thing.
They are if SQL is running in single user mode. Challenge appears to be to find what app is stealing the single connection.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2011 at 6:23 am
GilaMonster (1/31/2011)
Brandie Tarvin (1/31/2011)
Box Admin and SQL Server admin are not the same thing.They are if SQL is running in single user mode. Challenge appears to be to find what app is stealing the single connection.
Might not be an actual app. It may be a domain account that keeps trying to re-acquire the connection every time it gets disconnected or killed. We have a few of those around our place.
Wouldn't the security log give that information?
January 31, 2011 at 6:27 am
Brandie Tarvin (1/31/2011)
Wouldn't the security log give that information?
It might, though depending what else is on the box there may be too much noise in there to make sense.
If SQL is set to audit logins success as well as login failure, then the messages should appear in the output of the running SQL process (and in the error log)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2011 at 7:01 am
Run a network trace, see what tried to connect on 1434, or run a netstat from a command prompt. that should tell you what socket is open.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply