February 23, 2017 at 8:41 am
Greetings:
I'm reading Peter Carter's book 'Securing SQL Server' and there is a brief discussion about Windows Authentication vs. Mixed Mode.
Currently my SQL 2014 instance is running in Mixed Mode - but only so that a very few instance level SQL Logins can gain access.
About 10 applications are supported on the server with maybe 2-3 of them requiring Mixed Mode.
The other applications do have sensitive data.
Are the security vulnerabilities of Mixed Mode such that it would be worth running two instances of SQL Server, one where all Mixed Mode access is handled and the other running in Windows Authentication Mode?
I'm wanting to get an idea of how much more secure Windows Authentication Mode is and does the thought of running in Mixed Mode start most DBA's heads on fire?
Thanks
February 23, 2017 at 8:58 am
I run Windows Authentication wherever I can. The control in a central location as well as a central enforcement of password rules is a significant benefit. I also like that this makes any attempt to access via sa useless. But some third party applications can't be made to work without the server in Mixed Mode. As for the risks, that seems to be a business decision as to the need to secure the data versus the costs of licensing two servers. Also, Windows Authentication is not a panacea. It is vulnerable to certain kinds of attacks (ie social engineering) just as Mixed Mode would be.
February 28, 2017 at 10:37 am
I've yet to work in a shop where Windows Authentication was an option for the same reason you are experiencing. Yes, it's better if you can do it that way but ever since MS introduced better policy control over SQL Auth passwords (enforced complexity and expiration) it's less of an argument. Along with the numerous ways you can lock down the sa account, I certainly wouldn't consider splitting the DB's out onto another server.
March 1, 2017 at 6:21 am
Thanks for the replies.
March 1, 2017 at 8:55 am
one of the primary reasons for separating databases and logins to a separate instance is where you have different security requirements. So, yes it is completely valid in your case
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 1, 2017 at 8:55 am
We run mixed mode where I work mostly because in the even that something horrible happens to AD, we might still want into the SQL servers. We have a group with very few users in it in AD and that is the only group that gets sysadmin access on the databases. Our IT department could delete that group or disable the group and we'd still be able to connect to our local machines, but then we'd have no account with sysadmin access to the database. We use mixed mode as a failsafe just in case something like that happeend.
That being said, we try to keep our SQL users to a minimum.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 1, 2017 at 10:10 am
bmg002 - Wednesday, March 1, 2017 8:55 AMWe run mixed mode where I work mostly because in the even that something horrible happens to AD, we might still want into the SQL servers. We have a group with very few users in it in AD and that is the only group that gets sysadmin access on the databases. Our IT department could delete that group or disable the group and we'd still be able to connect to our local machines, but then we'd have no account with sysadmin access to the database. We use mixed mode as a failsafe just in case something like that happeend.That being said, we try to keep our SQL users to a minimum.
if AD "fails" you have bigger issues than not being able to connect to your sql server :hehe:
If your AD DS is really that unstable you should look to improve it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 1, 2017 at 10:21 am
Perry Whittle - Wednesday, March 1, 2017 10:10 AMbmg002 - Wednesday, March 1, 2017 8:55 AMWe run mixed mode where I work mostly because in the even that something horrible happens to AD, we might still want into the SQL servers. We have a group with very few users in it in AD and that is the only group that gets sysadmin access on the databases. Our IT department could delete that group or disable the group and we'd still be able to connect to our local machines, but then we'd have no account with sysadmin access to the database. We use mixed mode as a failsafe just in case something like that happeend.That being said, we try to keep our SQL users to a minimum.
if AD "fails" you have bigger issues than not being able to connect to your sql server :hehe:
If your AD DS is really that unstable you should look to improve it
While I do agree, what would you do if some software update mucked around with the firewall settings and then your SQL instances and Windows could no longer communicate to AD?
As I have no control or access to any DC's, I can only manage the things I have access to. And I like to be prepared for a "worst case" scenario. If we do have a catastrophic event where our DC's go down, my windows credentials on my workstation are cached so I can still log in. I can then verify that the things I mangage (SQL) are still running properly and I can connect to them. After that it's just waiting for AD to come back up and normal work can resume.
Mixed mode gives me a bit of a safety net. And presuming all of the DC's went down at once, I can still connect to SQL and do things on the databases which I manage to ensure that they are all safe (dbcc checks for example). Could be disk issues. We have had some unexpected downtime due to check disk running at unscheduled times.
Since I am the DBA where I work and can't touch the AD systems, if AD fails it isn't my problem :P. If SQL fails, that is my problem.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 1, 2017 at 10:43 am
bmg002 - Wednesday, March 1, 2017 10:21 AMWhile I do agree, what would you do if some software update mucked around with the firewall settings and then your SQL instances and Windows could no longer communicate to AD?
As I have no control or access to any DC's, I can only manage the things I have access to. And I like to be prepared for a "worst case" scenario. If we do have a catastrophic event where our DC's go down, my windows credentials on my workstation are cached so I can still log in. I can then verify that the things I mangage (SQL) are still running properly and I can connect to them. After that it's just waiting for AD to come back up and normal work can resume.Mixed mode gives me a bit of a safety net. And presuming all of the DC's went down at once, I can still connect to SQL and do things on the databases which I manage to ensure that they are all safe (dbcc checks for example). Could be disk issues. We have had some unexpected downtime due to check disk running at unscheduled times.
Since I am the DBA where I work and can't touch the AD systems, if AD fails it isn't my problem :P. If SQL fails, that is my problem.
I would guess you'd likely still be able to get in with cached credentials though.
Sue
March 1, 2017 at 10:53 am
Sue_H - Wednesday, March 1, 2017 10:43 AMbmg002 - Wednesday, March 1, 2017 10:21 AMWhile I do agree, what would you do if some software update mucked around with the firewall settings and then your SQL instances and Windows could no longer communicate to AD?
As I have no control or access to any DC's, I can only manage the things I have access to. And I like to be prepared for a "worst case" scenario. If we do have a catastrophic event where our DC's go down, my windows credentials on my workstation are cached so I can still log in. I can then verify that the things I mangage (SQL) are still running properly and I can connect to them. After that it's just waiting for AD to come back up and normal work can resume.Mixed mode gives me a bit of a safety net. And presuming all of the DC's went down at once, I can still connect to SQL and do things on the databases which I manage to ensure that they are all safe (dbcc checks for example). Could be disk issues. We have had some unexpected downtime due to check disk running at unscheduled times.
Since I am the DBA where I work and can't touch the AD systems, if AD fails it isn't my problem :P. If SQL fails, that is my problem.
I would guess you'd likely still be able to get in with cached credentials though.
Sue
Does SQL use cached credentials in the event that AD goes down? I thought that SQL didn't cache credentials.
Learn something new every day.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 1, 2017 at 10:55 am
I'm a little skeptical of the concerns regarding the AD going down. In our environment, if that happened nobody would be able to use their workstations and they wouldn't be able to use any applications that use the database. I suppose if a web server was using a database you wouldn't want it's logins to authenticate against the AD, but I'm thinking of putting all web facing databases on a separate server - away from sensitive data.
Interesting nonetheless.
March 1, 2017 at 11:08 am
thelenj - Wednesday, March 1, 2017 10:55 AMI'm a little skeptical of the concerns regarding the AD going down. In our environment, if that happened nobody would be able to use their workstations and they wouldn't be able to use any applications that use the database. I suppose if a web server was using a database you wouldn't want it's logins to authenticate against the AD, but I'm thinking of putting all web facing databases on a separate server - away from sensitive data.Interesting nonetheless.
Windows caches the login credentials so users could still likely log into their workstations. Easy way to test this is to log out of your machine, unplug the network cable and then log in again. It will likely succeed.
I am going to try this with SQL later on today as I am curious about that. If it works, I'm going to be bumping a lot of our servers out of mixed mode.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 1, 2017 at 12:38 pm
I like the safety net of an alternative means of access. Also, our application relies on mixed mode to work.
Also, I have worked in environments that used web-based VPN software and you could not use Windows Authentication to access the servers. Sucked.
March 1, 2017 at 12:49 pm
bmg002 - Wednesday, March 1, 2017 11:08 AMthelenj - Wednesday, March 1, 2017 10:55 AMI'm a little skeptical of the concerns regarding the AD going down. In our environment, if that happened nobody would be able to use their workstations and they wouldn't be able to use any applications that use the database. I suppose if a web server was using a database you wouldn't want it's logins to authenticate against the AD, but I'm thinking of putting all web facing databases on a separate server - away from sensitive data.Interesting nonetheless.
Windows caches the login credentials so users could still likely log into their workstations. Easy way to test this is to log out of your machine, unplug the network cable and then log in again. It will likely succeed.
I am going to try this with SQL later on today as I am curious about that. If it works, I'm going to be bumping a lot of our servers out of mixed mode.
That's what I meant earlier. Another thought I just had and can't test now is what if you had a local account on that server in addition to your domain account? That could be another avenue.
Sue
March 2, 2017 at 8:33 am
Sue_H - Wednesday, March 1, 2017 12:49 PMbmg002 - Wednesday, March 1, 2017 11:08 AMthelenj - Wednesday, March 1, 2017 10:55 AMI'm a little skeptical of the concerns regarding the AD going down. In our environment, if that happened nobody would be able to use their workstations and they wouldn't be able to use any applications that use the database. I suppose if a web server was using a database you wouldn't want it's logins to authenticate against the AD, but I'm thinking of putting all web facing databases on a separate server - away from sensitive data.Interesting nonetheless.
Windows caches the login credentials so users could still likely log into their workstations. Easy way to test this is to log out of your machine, unplug the network cable and then log in again. It will likely succeed.
I am going to try this with SQL later on today as I am curious about that. If it works, I'm going to be bumping a lot of our servers out of mixed mode.That's what I meant earlier. Another thought I just had and can't test now is what if you had a local account on that server in addition to your domain account? That could be another avenue.
Sue
While I think that would work, we like to keep accounts on the physical servers to a minimum.
I suppose in the end there are a lot of alternates and I'm not sure if there is a "best practice" for authentication types, eh? It seems like it is a lot of DBA preference and software requirements.
Although I am going to test out the cached credentials connecting to SQL Server at some point. I realized my previous thought on this test was dumb because I don't run SQL Server locally at the moment so pulling my network cable would mean no access to SQL at all so not a good test. I'll need to spin up a SQL instance locally to try it out.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply