April 1, 2010 at 8:13 am
Apologies in advance if this question has already been answered, but I have been doing some reading on this subject have conflicting information and thought I’d ask the question here to see can I get a straight answer.
My company is setting up a SQL 2008 server which we are going to place on a DMZ along with an active directory controller. In another DMZ we are setting up an IIS server that will connect into the IIS server using connection strings to access data on the SQL side. We have been doing this up to now on IIS6 and SQL2000 using normal connection strings and using mixed mode authentication on the SQL Server.
In the new setup it is advised to use Windows Authentication mode only, is this correct and is this the best way? I understand windows Authentication mode is probably best within the work LAN, but I’m not sure I like the idea of the IIS server containing connection strings with domain/user information. I know supplying SQL information in the connection string isn’t much better, but wondering which is the less of two evils so to speak?
While I’m on the subject of security on the IIS side, just a quick question, is encrypting the connection string the best method to ensure a hacker doesn’t gain valuable information about security details on your SQL box by looking at the security information contained within the string if the IIS box was comprised?
Thanks in advance for any information anyone can supply.
Sliver light
April 1, 2010 at 8:34 am
In the new setup it is advised to use Windows Authentication mode only, is this correct and is this the best way? I understand windows Authentication mode is probably best within the work LAN, but I’m not sure I like the idea of the IIS server containing connection strings with domain/user information. I know supplying SQL information in the connection string isn’t much better, but wondering which is the less of two evils so to speak?
While I’m on the subject of security on the IIS side, just a quick question, is encrypting the connection string the best method to ensure a hacker doesn’t gain valuable information about security details on your SQL box by looking at the security information contained within the string if the IIS box was comprised?
Thanks in advance for any information anyone can supply.
In a web application mixed mode is the best but if you must use Windows authentication you should use the Asp.net AD membership provider so you don't run into issues. The reason is Windows authentication in an Asp.net application will run into scale issues with a few thousand users. The other thing to do is just use mixed mode and the SQL Server membership provider.
To encrypt the connection string that depends on your needs that is what your application is doing and your users.
Kind regards,
Gift Peddie
April 1, 2010 at 8:37 am
Are you using anonymous access to IIS? Typically you put the domain account and pwd in the IIS setup and it's not visible to users of the web app. You'll have to authenticate somehow.
If you pass through the authentication of the user of the web app, then you aren't storing anything, but the user has to enter it.
I would learn towards the Windows auth for anonymous connections since it moves the security away from the application, and puts it on IIS.
April 6, 2010 at 8:16 am
Hi Folks,
Thanks for the responses you gave, just to clarify our application uses just one domain account to access the information contained within the DB, i.e. all security is done at an application level. Judging from the responses you gave it seems like Windows Authentication mode is the best route to take?
Just from the security side I was more concerned with the IIS box been compromised and although as difficult as it might be the hacker gaining access to the registry to get the account information for the domain with the sql server on it from the connection string... I've seen a couple of ways to encrypt the connection strings, seems like this is the most secure way or maybe someone else has a better solution.
Thanks Again for the help.:-)
April 6, 2010 at 8:27 am
I think it's harder to get the password from the IIS config for an anon user than from a flat file somewhere. I could be wrong, but it's likely more secure there.
So Windows Auth for IIS to connect to the db, and then your app handles all login/internal security from there.
April 6, 2010 at 8:31 am
Cheers for that Steve, we'll have a look at putting it in the IIS side... Thanks:-)
April 6, 2010 at 8:33 am
for windows authentication the user name and password is not in the connections string, not is it in the registry.
The probability of survival is inversely proportional to the angle of arrival.
May 18, 2010 at 4:18 am
Hi All,
Sorry for digging this old post up.. But I was just looking for some clarification in relation to some issues I have encountered in trying to get Windows Authentication Mode setup and running correctly.
To recap I have an IIS server located in a DMZ which is not part of the Domain on which the SQL server in located in. I'm not sure if I outlined this before. The IIS server is a standalone machine and I'd like it to remain that way. The SQL server is in Windows Authentication mode only.
The Primary issue that I have been having is trying to connect to the SQL server from the IIS server. I always get the following error
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
I can't use any user information in the connection strings because it's Windows Authentication on the SQL server only.
I have tried setting up SPN's at the application level for SQL to get around this issue.. But they don't seem to work.
The only solution that seemed to work for me was to create duplicate accounts on the IIS and on the local SQL machine, with the same passwords and ensure that the application pool on the IIS server runs under this account and that on the SQL server this account has sys admin privs. It's more of a workaround and I'm not so happy it's the best way to achieve what I need.
I'm beginning to wonder if mixed mode authentication on the SQL server is the better way for me to achieve what I need for these connections and encrypt the connection strings on the IIS side so to help security.. Can anyone tell me the best recommended approach for this.
Thanks for any advice you can give..
Sliverlight.
May 18, 2010 at 7:02 am
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
This is double hop related the Asp.net account is not recognized by the Windows server running SQL Server.
I have tried setting up SPN's at the application level for SQL to get around this issue.. But they don't seem to work.
Not relevant to Asp.net which is an application context.
The only solution that seemed to work for me was to create duplicate accounts on the IIS and on the local SQL machine, with the same passwords and ensure that the application pool on the IIS server runs under this account and that on the SQL server this account has sys admin privs. It's more of a workaround and I'm not so happy it's the best way to achieve what I need.
The account does not have to be a sys admin account because you could also use the network service account of the server running IIS and add it to the server running SQL Server, SQL Server on the server level and in the database. This is a valid solution.
Kind regards,
Gift Peddie
May 18, 2010 at 7:40 am
Appreciate your comments... just added my own thoughts in below.
I have tried setting up SPN's at the application level for SQL to get around this issue.. But they don't seem to work.
Not relevant to Asp.net which is an application context.
SL -----> This procedure seemed to be outlined in a couple of different sites as a way to achieve authentication against the SQL from the IIS application using Kerberos, here's one such link
http://www.sql-server-performance.com/articles/dba/kerberos_protocol_transition_p2.aspx%5B/i%5D
The only solution that seemed to work for me was to create duplicate accounts on the IIS and on the local SQL machine, with the same passwords and ensure that the application pool on the IIS server runs under this account and that on the SQL server this account has sys admin privs. It's more of a workaround and I'm not so happy it's the best way to achieve what I need.
The account does not have to be a sys admin account because you could also use the network service account of the server running IIS and add it to the server running SQL Server, SQL Server on the server level and in the database. This is a valid solution.[/quote]
SL -----> While I understand it might be a valid solution, I guess the question I was trying pose is, is it the best solution? From what I have seen so far... I can't see what advantages windows Authentication is giving me over mixed mode authentication on the SQL server, when the SQL server and IIS server are not on the same domain.. apart from making life a little more difficult to connect to it from an non domain machines/accounts.
So far I have read that Windows Authentication is the most secure solution, while I understand this might be the case in an intranet, I don't see what advantages it is giving me in the environment that I have described. A part from not having a connection string. I wonder in this case what authentication people are going towards?
May 18, 2010 at 8:13 am
SL -----> This procedure seemed to be outlined in a couple of different sites as a way to achieve authentication against the SQL from the IIS application using Kerberos, here's one such link
http://www.sql-server-performance.com/articles/dba/kerberos_protocol_transition_p2.aspx%5B/quote%5D
I have looked at the article you posted the code there is invalid because when you are using Windows authentication impersonation is supposed to be false, so I think you need to go look up Windows ACL(access control list) propagation so you will know constrained delegation works in networks with skilled admins.
SL -----> While I understand it might be a valid solution, I guess the question I was trying pose is, is it the best solution? From what I have seen so far... I can't see what advantages windows Authentication is giving me over mixed mode authentication on the SQL server, when the SQL server and IIS server are not on the same domain.. apart from making life a little more difficult to connect to it from an non domain machines/accounts.
The double hop issue is not domain related but rather server related that is when IIS and SQL Server in separate servers you have double hop which means you could configure kerberos and hope it works or add the account as I said earlier and know it will always work.
So far I have read that Windows Authentication is the most secure solution, while I understand this might be the case in an intranet, I don't see what advantages it is giving me in the environment that I have described. A part from not having a connection string. I wonder in this case what authentication people are going towards?
I covered that in my original reply.
Kind regards,
Gift Peddie
May 18, 2010 at 8:16 am
If you have separate domains, I don't think Windows Auth gives you much, and the need to sync passwords, to me, is a problem. Might as well be on the same domain then.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply