0

According to MSDN when constructing a connection string for SQL Server:

If User ID and Password are specified and Integrated Security is set to true, the User ID and Password will be ignored and Integrated Security will be used.

Now the question is why the data provider does not let the user to hard code a specific Windows username and password in connection string?

How is this worse than hard coding a SQL username and password?

Isn't it better to configure SQL server to only accept Windows logins (basically disable SQL authentication) and on client side specify Windows username and password, rather than running SQL server in mixed mode (both Windows and SQL authentication enabled) and create a SQL user for every single database server/instance and specify SQL username and password in connection string?

At least when using Windows authentication you just need to manage one domain user, but in case of SQL Server authentication you have to manage a user per SQL server/instance.

kaptan
  • 3,060
  • 5
  • 34
  • 46
  • I think I'm missing something here. What are you trying to do? – lc. Jul 09 '13 at 01:53
  • @Ic, I am developing a .net web application that talks to bunch of sql servers. in order to setup the connection string for sql server either i have to use sql authentication or windows authentication. if i use windows authentication then the credentials of the user who is running my app will be used to connect to sql server which is not the desired behaviour i am after. basically i just want to use the username and password of a windows domain user in connection string. – kaptan Jul 09 '13 at 02:14
  • 1
    @kaptan see my answer at http://stackoverflow.com/questions/24834411/how-can-i-impersonate-a-user-from-a-different-untrusted-domain-the-way-windows for a way you can accomplish this. – Hannah Vernon Oct 03 '14 at 21:56

1 Answers1

0

The point of integrated security is so that you aren't hard coding a username and password. This prevents an attacker who might gain access to the machine from reading the configuration file and learning domain usernames and passwords.

It's a security mechanism that mitigates machine compromising.

Erik Funkenbusch
  • 92,674
  • 28
  • 195
  • 291
  • the problem is that because of this restriction i have to use sql authentication and i have to provide sql username password anyway in the connection string, because this a connection string in web.conf of a web application and i do not want to use the credentials of the user that runs my web app to connect to sql server. so why not let the user specify the username and password for a specific windows domain user and at the same time encrypt the web.config? basically i am saying that the user should have all the options to make a better decision. what would you do in case of a web application? – kaptan Jul 09 '13 at 02:24
  • You are doing it wrong. Your web application should be running as an App Pool identity, these identities run as the Network Service account. Network Service connects to SQL server as the local machine name, not as the username. In this way, you give the authenticated domain machine access to the SQL server. – Erik Funkenbusch Jul 09 '13 at 02:37
  • 1
    Still, sql username password exposure is better than domain username password exposure. If an attacker gains access to login to your sql server, it can only execute queries. If they gain a domain user name/account, they can do a lot more damage. They can only login to the SQL server with a sql login, but with domain credentials they can log into any machine on the network the user has access to. See http://www.iis.net/learn/manage/configuring-security/application-pool-identities – Erik Funkenbusch Jul 09 '13 at 02:38
  • I understand what you are saying about application pool identity but this would work fine when you want to deploy your application on one iis server. When you have multiple iis servers that you need to deploy your application on, you have to create security login for every single application pool identity which I believe is not efficient in a sense. There is maintenance overhead involved in managing all these application pool identities. – kaptan Jul 09 '13 at 18:46
  • On the other hand if you create a domain user and restrict it with group policy to only have access to certain sql servers and only give it the permissions it needs, then you have better control. Basically rather than having tens of identities to manage, you only need to manage one domain user. – kaptan Jul 09 '13 at 18:49
  • @kaptan - Look, you can argue until you're blue I the face. Microsoft did it the way they did it because they believe it's more secure to do so. We can't change that. If you're bound and determined, you can setup a central administration sql server that is used by all your servers. http://msdn.microsoft.com/en-us/library/bb934126.aspx – Erik Funkenbusch Jul 09 '13 at 20:06
  • The fact that MS did it that way does not mean that it is the correct way. I am just trying to make a constructive discussion around why MS made that choice. I don't know why you need to use such unprofessional language in your comment though! – kaptan Jul 10 '13 at 17:46
  • @kaptan - I used no unprofessional language. What are you talking about? "discussions" are off topic for Stack Overflow, this is a question and answer site, not a discussion forum. Even so, there's absolutely nothing constructive that can come from any discussion here, we have no control over this. You asked why you can't do something, I told you why. – Erik Funkenbusch Jul 10 '13 at 19:06