February 24, 2022 at 6:30 am
Hi
I have a SQL Server 2014 Enterprise Edition instance running on Windows 2012 O/S where I do not have the 'sa' password. I inherited the server after the previous DBA left and I am not sysadmin on the server therefore I am not able to perform DBA tasks such as backup/restore DB, attach DB, check SQL Error logs etc.
I attempted to retrieve the 'sa' password using the following method:
My problem is that I am not able to connect. I get the error that indicates that only one administrator can connect as the instance is in Single User mode. However I set the flag to allow only SQLCMD to connect. And no one else in that split second could have connected using SQLCMD. Do note that I started SQLCMD with the same case as this is case sensitive. I have also stopped and changed to manual start for SQL Agent and Reporting Services that are on the server.
Please help as I have a database that has gone OFFLINE on the server and it is a business application database.
Regards,
Iggy
February 24, 2022 at 8:06 am
Is the service running as a domain account?
You could run SSMS as the domain account and login that way instead of using the -m switch.
Then add in your account to manage the system instead.
You have looked to have done everything correct, but if something else is taking a sqlcmd session.
if this is a VM, disconnect the NICs and connect via the Hypervisor console instead.
More than likely something external is polling for a connection with SQLCMD or there is something local using SQLCMD so verify in task manager if any other SQLCMD sessions are active.
March 16, 2022 at 12:05 am
This is a bit late and I hope you already figured it out. In the case that you have not, here is how I do it.
MSTSC -v [servername] /f -console /admin
2. Once you have a successful RDP connection going on the server hosting the SQL instance, open a command prompt running as an administrator and then type. Replace "mssqlserver" with the instance name if it is not the default SQL instance.
net stop mssqlserver
net start mssqlserver /m"SQLCMD"
SQLCMD -E -S [servername]
3. At this point you should have passed the hurdle "only one administrator can connect" and you should be able to create a login and assign it to the sysadmin SQL Server role.
By the way, you must be a member of the local admin group at the OS level on the server running the SQL instance for this to work.
[font="Verdana"]Sal Young[/font]
[font="Verdana"]MCITP Database Administrator[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply