June 29, 2009 at 10:44 pm
Hi Friends,
I have a doubt on SQL Security.
I am working on SQL 2005 machines.
My question is , suppose someone (asy my Jr. DBA) has deleted the BUILTIN\ADMINISTRATORS group from the sysadmin role and i also disabled my "sa" Login. How would i get back or is there any way to recover my sa password or else re-installation is the Solution??????
Any thoughts will be greatly appreciated.
Thank You!
June 29, 2009 at 10:58 pm
So, you are saying that the only two accounts/groups with sys admin rights to your server were the Builtin\Administrators and sa, correct? First, as a general rule, you shouldn't delete the Builtin\Administrators group until you had at least 3 tested alternate ways to access your server with a sys admin account.
If I remember correctly, and you may have to search SSC for this, you may be able to get sys admin access by restarting SQL Server in single-user mode. I haven't had to do this myself, as I have at least 3 tested means of access my servers with a Sys Admin account.
June 30, 2009 at 12:25 am
Your Solution my friend :
Repro of issue :
1) Delete the Builtin/administrator account .
2) tested it through sqlcmd and got the error 18456 Level 14 State 1.
3) assume that i have forgotten the SA password as well.
Solution:
1) Stop SQL Server service and start it with -m
2) go to C:\Program Files\Microsoft SQL Server\90\Tools\Binn through cmd prompt
3) type sqlcmd -E and hit enter .If its named instance then sqlcmd -SServer\instance -E and hit enter.
4) you will get > sign
5) commands you need to use
use master
go
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
go
6) You are done exit out of it .
7) restart you SQL Server service without -m parameter.
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 30, 2009 at 3:15 am
Hi Abhay,
Excellent!!!
Thanks a lot. it worked fine.
But again when i am try to connect using sqlcmd , am getting an Error.
Do you have any idea on that?
Am attaching the screen shot. pl help me out!
How could i get rid of this error dude???
Thanks in Advance.
June 30, 2009 at 3:27 am
It says that your login has timed out ..
Internally SQL might be busy or something unusual going on internally like resource contention ..
Do one thing : restart SQL Server and connect through Management studio ...
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 30, 2009 at 3:35 am
check you sqlserver instance ERRORLOG file to see if the startup is OK.
You may also want to try to add your windows account to the local server group called
computername\SQLServer2005MSSQLUser$computername$instance
In my case that is:
My pc's name WS20098002
My sqlinstance name SQL2005DE
WS20098002\SQLServer2005MSSQLUser$WS20098002$SQL2005DE
This local windows group is member of sqlserver sysadmin.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 30, 2009 at 8:18 am
Thanks Guys!
June 30, 2009 at 8:33 am
Hey Abay,
I have done a big blunder man!
This is what i have done. Actually , i was doing trail and error on SQL Security.
First thing, My SQL Server is not in mixed mode.
Secondly, "sa" is disabled.
Only i left with Windows Authentication.
What i have done is i have removed the BUILTIN\Adminstrators from the sysadmin role. But the Login still exist.
Now i stopped my SQL Server.
Restarted my SQL Server in single user mode, as you said (step given above)
> Created the login using sqlcmd and came out.
Now restrated my SQL server in mult-user mode removing -m option.
Now able to connect to Mgmt studio using Windows Authentication.
I tried to enable "sa" but it not working. Also, tried to chnge the Authentication mode to Mixed Authentication but i am getting error saying no permisison to write to registery.
Then i came to know that i have been removed from sysadmin Server role.
Only thing, i have left is , i thought of connecting to the SQL Server from cmdline and add him to sysadmin role saying
sqlcmd -Smssql$sql_02 -E
>use master
>go
>EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin'
GO
This is my actual intension!!!
without ading hime to sysadmin role, i cannot enable Mixed mode and enable / chnage the password for "sa".
But while trying to connect, i am getting the Error which i have attached in the above screen shot.
The Error still persists!!!!!!!!!!
Any thoughts :-((((((((((
June 30, 2009 at 12:04 pm
For the moment, add the service account you are using to the windows local administrators group of your server.
Maybe best to stop / start your sqlinstance.
Then you should be able to switch to mixed auth mode.
After that, remove your service account from the windows local adminstrators group !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 30, 2009 at 9:10 pm
Its good to try this option .Once you are the Admin on the server , you can do anything ..But you messed big time my friend 🙂 ...this is how we learn ...I also did similar mistakes in the past ...
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 30, 2009 at 10:55 pm
Hi Guys,
You right on!
It worked out very well.
Two more doubt in this topic.
First one, as Abhay was mentioning using the sqlcmd -S -E,
what is the use of -E. it is said that it is trusted connection. What user or what impersonation or does it take Windows login (Windows Authentication).i.e. which credentials or what is happening when am not mentioning any username and pwd in the command line.?????
Secondly, the Error which i had attached am getting that one very frequently, in such am using Mgmt studio to connect to sql server. Can anyone figure where exactly and how to troubleshoot the error????
Thank You!
July 1, 2009 at 12:07 am
Yes if you are using a Trusted connection then SQL will use Windows authentication .The othe option is to use login and password (do Sqlcmd /? to know more)
Secondly , connection timout will happen for example when you the database is in single user mode and you are hitting one more connection .I know this is not you case as you would have restarted SQL Server with out -m .
If you are still getting it you will have to dig further probably checking the errorlogs will be a good idea .There might be some other issue due to which you are not even getting the small memory for logging in to the SQL Server .But honestly , I am not sure about it .
you can also try DAC connection if you are hitting this kind of issue .But DAC needs you to be in single user mode i.e. using -m. The option for DAC is -A .So the connection string will be sqlcmd -A -S""
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
July 1, 2009 at 4:47 am
hi_abhay78 (6/30/2009)
Its good to try this option .Once you are the Admin on the server , you can do anything ..But you messed big time my friend 🙂 ...this is how we learn ...I also did similar mistakes in the past ...
Indeed, every once in a while a decent dba needs to prove (s)he's human after all :w00t:
Sharing these mishaps and the resolution is what makes this such a strong forum !:cool:
Been there.... done that ... 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 1, 2009 at 4:42 pm
Coming back to my question, the BUILTIN\Administrators has been removed
from my SQL Server and you are saying that -E switch takes windows
authentication and since the user belongs to Administrator group at
Windows Level, i can start the sql server services. Till here, am
convinced. But, when we are connecting to the SQL Server using
>sqlcmd -Smssql$sql_01 -E and trying to create a login i.e
[Builtin\Administrators], that means it has some admin privilegs and
that is the reason why he can able to create a login and add him as a
member of "sysadmin" server role and again when we restart the sql
server in multi-user mode, i can login as "Windows Authentication",
since [Builtin\Administrators] login is created and belong to sysadmin
server role, we can enable the "sa" password. Right!!!
Now my question, you said -E is a trusted connection and takes Windows
Authentication. That means he belongs to [Builtin\Administrators] group
(plz correct me if am wrong) and by that time, [Builtin\Administrators]
is not existing in sql server and what permissions does it have when i
am using -E. If am not wrong then it should'nt be Windows
Authentication or belong [Builtin\Administrator] group. He is having
special permissions , that is why he can able create a login and adding
him to the "sysadmin" server role. Can you please elaborate on this ???
I am just trying to understand what is happening over there!!!!
Please help!
Thank You.
February 18, 2010 at 1:21 am
Hi
When i tried this pswd recovery it shows me the error timed out.
Tell me how could i proceed furthur.
Thanks
Padma
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply