July 27, 2015 at 3:18 am
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.
July 27, 2015 at 3:32 am
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
July 27, 2015 at 3:37 am
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.
July 27, 2015 at 3:44 am
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
July 27, 2015 at 3:48 am
Thank you for your answer , I will insvestigate end test our tools to see if this changing the auth mode.
July 28, 2015 at 9:44 am
Check if you have SQL Policy Management running , it may be a policy set to enforce instead of evaluate the setting?
July 28, 2015 at 10:02 am
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.
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/
August 6, 2015 at 11:52 pm
Use regmon in https://technet.microsoft.com/en-us/sysinternals/bb896645, and lock down the box :).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply