SQL Login Mode Issue

Question

SQL Login Mode Issue

Christophe-470259

SSC Journeyman

Points: 77
More actions
July 27, 2015 at 3:18 am

#320075

Hello,
I m having an issue with SQL login mode, the version of SQL server doesn't really matter as I have this since SQL 2000 and until the latest version I m using (2008 R2 SP2, 2012 etc..)
We are software development company, our application is using SQL authentication, so the SQL server is set to Mixed Model ( Login Mode = 2 in regedit)
The problem is randomly , but almost all the time, after the Windows server is restarted, for maintenance, or due to Windows update, then the SQL Login Mode switch itself to 0 or 1 , IT means that user cannot login anymore on our Application until I have reset the Login mode and restart the SQL server service.
If some one already had this issue and know a fix, it will be really appreciated.
Thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

Someone's changing it, or a job is changing it. Since the setting only takes effect at restart, you see it after a windows restart. Ask the people with administrator access to the SQL instance who's changing the setting.

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

Christophe-470259 SSC Journeyman Points: 77 More actions · Answer 2

No one phisycally is changing it , as even when I restart the server myself it is doing it.

Could be one of our tools that run daily backup normally or manage other stuff with our database, like synchro to an online SQL DB etc ?

Thank you for your reply.

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

SQL's not going to change the setting automatically. Windows updates aren't. That leaves someone changing it or some job or application you have that's changing it.

The setting only takes effect on restart, it could have been changed any point prior to the restart after the previous restart and only when you restarted SQL did it take effect.

Start by talking to the people, then check jobs or custom tools you have, see if they're changing things.

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

Christophe-470259 SSC Journeyman Points: 77 More actions · Answer 4

Thank you for your answer , I will insvestigate end test our tools to see if this changing the auth mode.

Justin Manning SA Hall of Fame Points: 3190 More actions · Answer 5

Check if you have SQL Policy Management running , it may be a policy set to enforce instead of evaluate the setting?

Michael L John One Orange Chip Points: 27038 More actions · Answer 6

I'm not sure if this applies or not, but I had the same issue with SQL 2008 on a 6 node (5 active, one passive) cluster.

http://blogs.msdn.com/b/psssql/archive/2010/07/09/why-is-my-sql-clustered-instance-changing-authentication-modes.aspx

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 7

Use regmon in https://technet.microsoft.com/en-us/sysinternals/bb896645, and lock down the box :).