Create login of sysadmin role on server having logon trigger

Question

Create login of sysadmin role on server having logon trigger

saum70

SSC Eights!

Points: 993
More actions
September 23, 2015 at 1:27 am

#399075

Hi,
All the logins with sysadmin role are locked out in one of the SQL server. Also in that instance Logon trigger is implemented which allows only certain users with Windows Authentication to logon.
This server is converted into single user mode by using command prompt. Now we are attempting to connect to SQLServer using command sqlcmd -S FTCPU3239 (since we do not have any other login) and create a new login with sysadmin role but are refused due to logon trigger. it says Login failed for login 'DomainName\UserName' due to trigger execution.
Please guide what should be done.
Regards,
Saumik Vora

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Connect via the Dedicated Administrative Connection. Connections made to that don't fire login triggers.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

saum70 SSC Eights! Points: 993 More actions · Answer 2

Hi,

Connecting via DAC requires to specify User Id and password. Since none of the login is available, SQL server cannot be connected. to connect via DAC I am using following command

sqlcmd -S ServerName -U userId -P password -A

Please reply.

Regards,

Saumik Vora

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

saum70 (9/23/2015)
Connecting via DAC requires to specify User Id and password.

No it doesn't. It requires a sysadmin login, SQL or Windows.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

saum70 SSC Eights! Points: 993 More actions · Answer 4

Exactly..all the logins with sysadmin role are locked out..Windows login is restricted by the trigger...how do I get in??

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

All the sysadmin logins? Or all the SQL authentication ones?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

saum70 SSC Eights! Points: 993 More actions · Answer 6

saum70

SSC Eights!

Points: 993

September 23, 2015 at 6:41 am

#1828755

Yes all the sysadmin logins

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

In that case, it's not the login trigger that's your problem, as a non-sysadmin account can't create a sysadmin login.

Restart SQL in single user mode and connect with a windows account that is local administrator on the server.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

saum70 SSC Eights! Points: 993 More actions · Answer 8

Gail I had mentioned in the first message that SQL was restarted in single user mode and then tried to create sysadmin login. however could not get logged in through Windows user as the trigger restricts it. Please check the message

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Then once you're restarted in single user mode, use the DAC to connect. Connections made via the DAC don't fire login triggers.

Start SQL in single user mode to get a sysadmin login (windows authent, an account that's local admin on the server) When SQL is in single user mode, all windows accounts that are local administrator can connect to SQL Server with sysadmin permissions, even if the windows account hasn't been created as a login

Connect via the DAC to bypass login triggers.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

saum70 SSC Eights! Points: 993 More actions · Answer 10

Thanks Gail. By login through local admin account, I could connect and unlocked the SQL logins.