March 2, 2017 at 11:00 am
bmg002 - Thursday, March 2, 2017 8:33 AMWhile 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.
I think with some things (definitely not all) there are procedures, configs, etc often called Best Practices but are more like "Ideal Practices". And we are rarely in "Ideal" environments or situations. Sometimes it becomes a matter of pick your poison.
Sue
March 2, 2017 at 11:53 am
Sue_H - Thursday, March 2, 2017 11:00 AMbmg002 - Thursday, March 2, 2017 8:33 AMWhile 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.
I think with some things (definitely not all) there are procedures, configs, etc often called Best Practices but are more like "Ideal Practices". And we are rarely in "Ideal" environments or situations. Sometimes it becomes a matter of pick your poison.
Sue
Cached credentials are fairly short lived for AD authentication. The token ages out pretty quickly. Further, cached credentials are only available for interactive users.
Personally, I see no strong reason to disable SQL Auth beyond politics.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 2, 2017 at 12:05 pm
SQLRNNR - Thursday, March 2, 2017 11:53 AMSue_H - Thursday, March 2, 2017 11:00 AMbmg002 - Thursday, March 2, 2017 8:33 AMWhile 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.
I think with some things (definitely not all) there are procedures, configs, etc often called Best Practices but are more like "Ideal Practices". And we are rarely in "Ideal" environments or situations. Sometimes it becomes a matter of pick your poison.
Sue
Cached credentials are fairly short lived for AD authentication. The token ages out pretty quickly. Further, cached credentials are only available for interactive users.
Personally, I see no strong reason to disable SQL Auth beyond politics.
Yeah..I know about the interactive, they would cache when using RDP but thanks for the info. I was under the impression that they don't expire unless used 10 times or something like that or if the defaults were changed in group policy.
I wasn't advocating for one way or another really. Just curious about the options.
Sue
March 2, 2017 at 7:54 pm
bmg002 - Thursday, March 2, 2017 8:33 AMSue_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.
Keep me posted if you do try it. I did some more digging and they don't expire, plenty of posts around that.
Cached credentials were designed exactly for this - plenty of examples of using it for when people are traveling with their laptops, need to log on throughout the week, troubleshooting when unable to connect to AD, etc. Interesting things about the pass-the-hash attacks and debates on those since the credentials are stored in the HKLM\Security hive.
But the one thing I did notice is that you can disable it by setting the number of cached credentials that can be stored to 0 (instead of the default 10 - 25) so you would probably want to check the registry to see if it's disabled or not. Check this key: HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\Current Version\Winlogon\
Value: CachedLogonsCount
March 3, 2017 at 7:21 am
bmg002 - Wednesday, March 1, 2017 10:21 AMwhat 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?
😀 Then it's likely that all comms between the sql server and application server have been shut and sql authentication wont help you anyway.
I'm sorry but there's no real valid reason for using sql auth unless you have some backward application thats been hardcoded to use a particular connection format :hehe:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 3, 2017 at 7:26 am
Perry Whittle - Friday, March 3, 2017 7:21 AMbmg002 - Wednesday, March 1, 2017 10:21 AMwhat 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?😀 Then it's likely that all comms between the sql server and application server have been shut and sql authentication wont help you anyway.
I'm sorry but there's no real valid reason for using sql auth unless you have some backward application thats been hardcoded to use a particular connection format :hehe:
Good point.
If the AD is down how are you logging onto your workstation?
,
March 3, 2017 at 7:27 am
BTW, I've found the back and forth in this thread to be valuable.
It has been my opinion for quite some time that the only way to really learn something on the internet is to throw an idea onto the table and allow PRO and CON voices to argue about it. It really is the best way to get the full story on any topic.
Thanks again.
March 3, 2017 at 8:00 am
thelenj - Friday, March 3, 2017 7:25 AMPerry Whittle - Friday, March 3, 2017 7:21 AMbmg002 - Wednesday, March 1, 2017 10:21 AMwhat 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?😀 Then it's likely that all comms between the sql server and application server have been shut and sql authentication wont help you anyway.
I'm sorry but there's no real valid reason for using sql auth unless you have some backward application thats been hardcoded to use a particular connection format :hehe:
Good point.
If the AD is down how are you logging onto your workstation?,
I've seen windows updates do strange things. Same with other software updates. Could be a GPO on the AD side got updated and broke the connection for AD authentication to the SQL servers. But that particular GPO isn't applied to production boxes so they can authenticate no problem. Then I would be unable to log into the server obviously, but I could still get into SQL to verify that the instance is still up.
I work in a manufacturing plant and from the production view, the software stops working they don't know what is wrong. Just that the application is no longer working. So they do a best guess as to who to talk to, and more often than not, the DBA is one of the first one contacted. So I would want some method to verify the database is up even if it can't talk to AD. After that I would want to do integrity checks to make sure there was no corruption due to this (with the exception of our large databases which can take hours to do, but those have 3rd party software that requires SQL authentication) while the IT department investigates why the box can't connect to AD. Depending on the estimated downtime, our software guys might even want me to set up a temporary SQL account so they can make their software connect to it. Downtime is expensive and diagnosing the problem software (GPO, software firewall, gremlins) can be a long and painful process.
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 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply