May 17, 2012 at 11:42 am
Hello SQL Server Central friends,
I am assisting our web developer in setting up a standard for connecting to internal database servers from both inside and outside of the DMZ.
Currently we have a mixed bag of SQL logins, Windows AD with ODBC, Machine logins mapped to roles, and anonymous access to replicated data.
I would really appreciate any feedback regarding the best practices for websites authenticating to SQL Server in both scenarios.
Also, if you could provide a link to an explanation or a brief description for setting up the recommendation that would be most helpful.
Thank you in advance for your valuable input!
HawkeyeDBA
May 17, 2012 at 12:07 pm
Hawkeye_DBA (5/17/2012)
Hello SQL Server Central friends,I am assisting our web developer in setting up a standard for connecting to internal database servers from both inside and outside of the DMZ.
Currently we have a mixed bag of SQL logins, Windows AD with ODBC, Machine logins mapped to roles, and anonymous access to replicated data.
I would really appreciate any feedback regarding the best practices for websites authenticating to SQL Server in both scenarios.
Also, if you could provide a link to an explanation or a brief description for setting up the recommendation that would be most helpful.
Thank you in advance for your valuable input!
HawkeyeDBA
I recommend using Kerberos wherever possible, NTLM pass-through if Kerberos is not an option, and SQL logins as a last resort.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 17, 2012 at 12:14 pm
I almost forgot, if you end up relying on NTLM make sure your OS is patched. Security holes in pass-through auth (leverages NTLM) existed until recently:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 17, 2012 at 12:24 pm
opc.three (5/17/2012)
Hawkeye_DBA (5/17/2012)
Hello SQL Server Central friends,I am assisting our web developer in setting up a standard for connecting to internal database servers from both inside and outside of the DMZ.
Currently we have a mixed bag of SQL logins, Windows AD with ODBC, Machine logins mapped to roles, and anonymous access to replicated data.
I would really appreciate any feedback regarding the best practices for websites authenticating to SQL Server in both scenarios.
Also, if you could provide a link to an explanation or a brief description for setting up the recommendation that would be most helpful.
Thank you in advance for your valuable input!
HawkeyeDBA
I recommend using Kerberos wherever possible, NTLM pass-through if Kerberos is not an option, and SQL logins as a last resort.
Curious of why you gave the following order? My personal preference is SQL logins but...wait for it..It depends. I personally like SQL Logins when I have control. If there is a utility that allows the password to be stored in a secure method. The reason is because it gives me more control over the account and I know I am the only one that has the password. The issue I find with Kerberos and NTML is that the services / job needs to have the password for the account.
Again this is only as strong as the method used to store the credentials.
May 17, 2012 at 1:15 pm
eccentricDBA (5/17/2012)
opc.three (5/17/2012)
Hawkeye_DBA (5/17/2012)
Hello SQL Server Central friends,I am assisting our web developer in setting up a standard for connecting to internal database servers from both inside and outside of the DMZ.
Currently we have a mixed bag of SQL logins, Windows AD with ODBC, Machine logins mapped to roles, and anonymous access to replicated data.
I would really appreciate any feedback regarding the best practices for websites authenticating to SQL Server in both scenarios.
Also, if you could provide a link to an explanation or a brief description for setting up the recommendation that would be most helpful.
Thank you in advance for your valuable input!
HawkeyeDBA
I recommend using Kerberos wherever possible, NTLM pass-through if Kerberos is not an option, and SQL logins as a last resort.
Curious of why you gave the following order? My personal preference is SQL logins but...wait for it..It depends. I personally like SQL Logins when I have control. If there is a utility that allows the password to be stored in a secure method. The reason is because it gives me more control over the account and I know I am the only one that has the password. The issue I find with Kerberos and NTML is that the services / job needs to have the password for the account.
Again this is only as strong as the method used to store the credentials.
My recommendation and thoughts on this start with a best practice published by Microsoft.
From Kerberos Authentication and SQL Server
As a best practice, we recommend that you use Kerberos authentication whenever possible for connections to an instance of SQL Server.
I went with NTLM second because that is the fallback should a Kerberos handshake fail. Seeing as how "it depends" usually rules the discourse here I concede that it will depend, on a lot of factors, many of which may not be technical in nature. For example some CTO's say with the wave of a noble hand that they do not think there should be a domain in their Web DMZ. That is not necessarily a technical reason but one that would prevent Kerberos from being a possibility in an org. SQL Logins are last on my list because securing their passwords is pretty hard to do, much harder in my opinion than say getting the built-in protection Windows provides for service account passwords for free.
One item where we agree is "It Depends" 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 18, 2012 at 2:03 pm
eccentricDBA (5/17/2012)
Curious of why you gave the following order? My personal preference is SQL logins but...wait for it..It depends. I personally like SQL Logins when I have control. If there is a utility that allows the password to be stored in a secure method. The reason is because it gives me more control over the account and I know I am the only one that has the password. The issue I find with Kerberos and NTML is that the services / job needs to have the password for the account.Again this is only as strong as the method used to store the credentials.
That is the problem... seldom have I seen a clean and highly secure way to store SQL Server credentials that are needed from dozens or even hundreds of web and process servers.
My personal preference for this is to actually have a credential authentication tier (server). All apps connect to this tier when they first start (if it is a service or a scheduled job) or from the session authentication code on the web side. Once they properly authenticate they are get the servers and passwords they need for their sessions or whatever job they need to do.
In this way passwords can be maintained (encrypted) securely and can be changed at any time or even at regular intervals and the application code does not care.
Having said that... we use integrated security for services and back end batch processes and SQL server logins from the web because of firewall configurations.
The probability of survival is inversely proportional to the angle of arrival.
May 31, 2012 at 6:45 am
Thank you friends for your input, it is much appreciated and interesting to hear what others are doing in this area.
I will take your ideas along with a few of my own and present them to our web admin.
Thanks again!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply