August 16, 2017 at 5:31 am
Did you tell it which server/instance to connect to this time?
Just like any connection, you need to specify the server, the authentication (trusted or username and password) and then any other connection options that are relevant.
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
August 16, 2017 at 5:44 am
If I try sqlcmd -S SERVER\Instance I receive a login failure 'The server is in single user mode. Only one administrator can connect at this time...
Same error for sqlcmd -E -S SERVER\Instance
Apologies if I am going around in circles. I am unsure why I am receiving that error.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
August 16, 2017 at 5:50 am
-S <server> -E should work. What's the error from the SQL Server 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
August 16, 2017 at 6:08 am
The error log records:
Login failed for user 'SERVER\User'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine]
Error: 18456, Severity: 14, State 5.
The log I expected to contain the information has an old timestamp (earlier today). The error above was taken from Program Files\Microsoft SQL Server\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER. I thought the error would have been logged in the instance Program Files\Microsoft SQL Server\Program Files\Microsoft SQL Server\MSSQL12.UPC.
The windows application log records:Event ID 18461
login failed for user 'SERVER\serviceadmin.' Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine]
Thanks
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
August 16, 2017 at 11:29 am
my guess is you are missing login in the sql server..
August 16, 2017 at 11:39 am
If you're connecting to the instance "SERVER\Instance", then the login error will be in that instance's error log or, if you started SQL from the command line, will be on screen. It won't be in the default instance's 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
August 16, 2017 at 2:10 pm
You may be experiencing what has happened to me before: a windows service is grabbing the single allowed connection to SQL Server as soon as it starts up and thus blocking your connection. Following the steps in the article below, determine what service is connected to TCP port 1433 (or whatever your MSSQL port is set to), and then terminate the process to free up the connection. In my situation it was an Azure hosted IaaS instance, and the service in question was something called SQLIaaSExtension. In your case it may be 3rd party backup, SSIS, or something like that.
https://blogs.technet.microsoft.com/askperf/2008/08/26/what-port-is-that-service-using/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 16, 2017 at 2:10 pm
Sounds like there could be an application hitting the only open connection before you are able to try and connect. What happens if you try this:
- Script out the following
net stop sqlagent$<instName>
net stop mssql$<instName>
net start mssql$<instName> -f-m
sqlcmd -S <servName>\<instName> -E
- Open up a command prompt under the "Run as administrator" context
- Copy/paste/run the commands you scripted out
Where <servName> is the machine name of your SQL Server and <instName> is the name of the instance.
Hopefully that will restart in single-user mode with limited resources and allow you to connect with the available connection.
Joie Andrew
"Since 1982"
August 17, 2017 at 1:55 am
Thanks for all your input. Rather embarrassingly (or fortunately) I decided to RDP to the server using the service account I used to install the application. Hey presto the account was a sysadmin. I then changed the sa password + added AD group to sysadmins. It's a shame I didn't try that some 5 hours earlier!
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
August 17, 2017 at 2:18 am
I just tried Joie response, results below:
C:\Windows\system32>sqlcmd -S SERVER\Instance -E
1> EXEC sp_addsrvrolemember 'DOMAIN\MyAccount', 'sysadmin';
2> go
Msg 15247, Level 16, State 1, Server SERVER\Instance , Line 1
User does not have permission to perform this action.
Msg 15007, Level 16, State 1, Server SERVER\Instance , Procedure sp_addsrvrolemember, Line 33
'DOMAIN\MyAccount' is not a valid login or you do not have permission
Thanks Phil..
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply