July 4, 2012 at 5:11 am
I have just run an SSIS package to transfer logins between a two servers. The package failed but seems to have removed all the NT logins. The issue is I disabled the sa login for security reasons and now cannot login! Is there any way i can enable the sa login??
HELP!!
July 4, 2012 at 5:14 am
Ensure you are an administrator of the Windows server where SQL is installed.
Start SQL in single user mode using the startup flag -m
Then you will be able to login to SQL using windows authentication, enable the SA account or add in your account normally with SA rights.
Stop SQL, remove the -m flag
Start SQL
Login
July 4, 2012 at 5:16 am
Will this work even if there are no AD accounts on there? At least i can't see any??
July 4, 2012 at 5:18 am
As long as you can login to the Windows console session as an account which is in the Windows Administrators group you will be able to login to SQL.
It effectly puts BUILTIN\Administrators in as a temporary security group to SQL with SysAdmin rights.
July 4, 2012 at 5:28 am
Does anyone know why the transfer logins task in SSIS has done this?
July 4, 2012 at 5:36 am
I'm unsure on how it removed them from the server as it shouldn't and I just tested a simple transfer of 1 SQL and 1 Windows login no problem.
For transfering logins, I usually tend to use the sp_help_revlogin procedure
July 4, 2012 at 5:44 am
This is part of a cluster and cannot bring it back on-line now using -m? Any ideas?
July 4, 2012 at 5:51 am
Pause all the passive nodes so SQL cannot fail over.
Then start SQL using -m and give it an application which you will connect with (Management studio or SQLCMD), as it is more than likely the cluster services getting the only connection to do the IsAlive checks.
Then you should be able to issue a CREATE LOGIN command to recreate the login and sp_addsrvrolemember to add that login to the sysadmin server role.
July 4, 2012 at 6:14 am
Can you elaborate on this bit?
Then start SQL using -m and give it an application which you will connect with (Management studio or SQLCMD), as it is more than likely the cluster services getting the only connection to do the IsAlive checks.
Is this for a command line?
July 4, 2012 at 6:17 am
The fist link I detailed constains the information on this, but from a command prompt you want to run something like the below
net start MSSQL$INSTANCENAME -m"SQLCMD"
or
net start MSSQL$INSTANCENAME -m"Microsoft SQL Server Management Studio - Query"
or if your in the SSCM you want to change the startup parameters of the SQL Instance to include -m"SQLCMD" etc.
July 4, 2012 at 6:27 am
I run this but get the error:
The SQL Server (EARPSQLCL05) service is starting..
The SQL Server (EARPSQLCL05) service could not be started.
A service specific error occurred: 17058.
More help is available by typing NET HELPMSG 3547.
July 4, 2012 at 6:31 am
whats the full error message from eventvwr?
17058 is usually cannot open a file, usually down to permission errors.
July 4, 2012 at 8:11 am
I finally got this working by putting the -m switch at the END of the startup parameters. I then started the SQL Service (while having the SQL cluster off-line) then used sqlcmd to issue an ALTER LOGIN sa ENABLE command. Then removed the -m parameter, restarted SQL and logged in as sa. Phew.
Thanks for all your help.
July 4, 2012 at 8:13 am
Glad you got it back and working again.
July 13, 2012 at 5:00 am
Useful information thank you,already prepared for such a predicament.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply