sa password is lost - need to add Sys Admin Privileges to another account
-
Hello all
I have a very frustrating problem.
About 20 SQL servers (instances) I administer do not have their sa credentials and no one knows these.
In most cases I can log in to SSMS with my domain admin account but then gets restricted in what I can see and what I want to do because I do not have the sys admin, sec admin privileges.
On the Dev servers I added these privileges using the single user mode + local server admin account technique.
But on the Production Servers I can't do this because I am not allowed to stop the SQL Service - which I have to, to put in to -m mode and then remove it.
Also, I can't reset the sa password because nobody knows if anything would break - there is no documentation and there are a quite a number of legacy systems.
Any suggestions you may have will be greatly appreciated.
Thanks in advance.:-)
-
if you are a local admin on the server, there is a technique using powershell to impersonate the service account running SQL, and add logins, and then add that login to sysadmin. that technique never has to stop and start the service, since it's just creating a plain old connection as the service account.
so you have to run powershell in
1: administrative mode,
2: on the server, not remotely,
3: and also already be a local admin on the server.
this was the first stackexchange link i found that had that technique, but it is not the exact same code i had used a year or two ago.
but it also has a dependency on this script ,s o you need that loaded into powershell memory:
i cannot find my original script, but i just tried the scripts above it locally on my dev box, but i was still getting errors, but that might get you started.
I'll dig through my library to find the original script i know worked on SQL2014, and report back if i find it.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell
Great, many thanks for this.
I did find and try the first powershell script. It didn't work 🙁
I used my domain admin account on the windows server (didn't create a local admin account - locally)
The script created the account, without the sysadmin role. And I was not able to add that either.
Now, I didn't know about the second script needed in the powershell memory. So that could be a reason.
Still, if you do manage to find the script, I would be most pleased if you can share it.
Many thanks
H
-
you can do the same thing effectively with a dedicated admin connection from the server,
again only if you are a local admin user on the server, and also only if the dedicated admin connection was enabled.
from the server, try to open a DAC in SSMS;
if you are successfully, the DAC overrides permissions,a nd lets you add yourself.
Because i KNOW the period is a shortcut name to the local default instance on my SQL server, i typed ADMIN:. into the connection for the servername, but the correct servername works as well of course.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Good Morning Lowell (at least it is where I am 🙂 )
You are great, thank you so much!!
I am sure it was the second script that needed to be loaded in PowerShell memory. I didn't know about it before. But Just now I tried it and it worked.
Thank you so much.
Now to raise the change control to apply to production.
Wish you a Happy New Year ahead!!
-
Lowell (12/29/2016)
if you are a local admin on the server, there is a technique using powershell to impersonate the service account running SQL, and add logins, and then add that login to sysadmin. that technique never has to stop and start the service, since it's just creating a plain old connection as the service account.so you have to run powershell in
1: administrative mode,
2: on the server, not remotely,
3: and also already be a local admin on the server.
this was the first stackexchange link i found that had that technique, but it is not the exact same code i had used a year or two ago.
but it also has a dependency on this script ,s o you need that loaded into powershell memory:
i cannot find my original script, but i just tried the scripts above it locally on my dev box, but i was still getting errors, but that might get you started.
I'll dig through my library to find the original script i know worked on SQL2014, and report back if i find it.
I'm going to have to show my security folks this one. Heh... and everyone says using xp_CmdShell is dangerous. 😛
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (12/30/2016)
I'm going to have to show my security folks this one. Heh... and everyone says using xp_CmdShell is dangerous. 😛not all that dangerous, I think, since it's already assuming some physical control of the server.
if I'm local admin on the server, and I remote desktop to it, I could stop and start the services to add myself in single user mode, not that much different permissions wise...I'm still using local admin rights, just to impersonate the service account instead of stopping and starting.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (12/30/2016)
Jeff Moden (12/30/2016)
I'm going to have to show my security folks this one. Heh... and everyone says using xp_CmdShell is dangerous. 😛not all that dangerous, I think, since it's already assuming some physical control of the server.
if I'm local admin on the server, and I remote desktop to it, I could stop and start the services to add myself in single user mode, not that much different permissions wise...I'm still using local admin rights, just to impersonate the service account instead of stopping and starting.
Agreed. What I said didn't come out exactly right. Allow me to explain...
We use xp_CmdShell a lot on all our boxes and with full approval of the internal (inside our department) security team. Unfortunately, the external (outside our department) "security" team and a bunch of auditors take exception to all of that even though I've tried to educate them. I've even advertised and demonstrated that I've actually used xp_CmdShell to increase security by making is so that certain users no longer need to have admin privs on several boxes to supposedly do their jobs by writing stored procedures that take care of what they needed to do. The biggest win was making it so they didn't need ANY privs on the SQL Server file system so that they could (gasp!) copy files from lord knows where to the server so that they could do BULK INSERTs and neither do they have a need to have ANY privs, never mind the admin privs they were previously afforded on certain remote boxes so that BULK INSERT (as just one example) could load a file from the remote box.
Of course, the users don't have sysadmin privs on the SQL Server box to run xp_CmdShell for this. They only have the privs to run the very tightly controlled stored procedures that do. There is no way that I'd let non-DBAs have the privs to execute xp_CmdShell directly even though their privs can be limited even there.
Even though that fine proof was and is still strong justification for the use of xp_CmdShell, there is still doubt in the minds of the external security group because of all the auditors that are untrained in the art but have incorrectly heard/been directed that xp_CmdShell is a security risk (it IS only when done incorrectly).
What you posted is a fine example of what you can do if you're an administrator on a box. In the hands of "users", it's too dangerous a thing to allow, which means the users shouldn't have such privs, not that the method shouldn't be allowed to true admins that need to do such a thing. I was actually happy to see what you posted because it adds yet another powerful justification for why our use of xp_CmdShell to do things in very tightly controlled stored procedures (which they can't even see the code for never mind change it) without the users having any direct privs on the boxes where the data is at.
It takes a bit of extra work on our part to do that because the users can't make changes that they may need, so we have to do it for them. With that in mind, I was asked to provide a very specific example of what internal users could do with the wrong privs, especially one that could be a hidden/possibly undetected danger to SQL Server, and your post fits the bill perfectly! :w00t:
To wit, I'm in heaven and I apologize for the short response which, looking back at it, certainly sounded like I was saying the method you posted was a problem and I thank you profusely for providing me with a full bucket of pork chops to further educate the external security team and the poorly informed auditors.
Happy, HAPPY, Joy, JOY! 2017 is going to be a pork chop blast partially thanks to your post. 😛 I can't wait for the next round of "Doubting Thomas" auditors! 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply