ASP.NET Connection Strings

[Zippeh]

When using SQL Server logins within ASP.NET connection strings, what is the most secure way of storing the connection string in the application?

Previously I have used the registry when using ASP and reading the connection string from there, but I am sure that there is a more secure way involving encryption of the connection string when using ASP.NET.

Could someone point me in the right direction as to how to store these securely and access them securely? Many thanks

 

[PTNL]

Using the Web.Config file is very useful for this. Just add the "using/Imports System.Configuration" line in the code-behind's declarations area, and you can reference items in the "applicationkey" tags. Just remember that stuff in the Web.Config is case-sensitive.

About 5 minutes of google-ing should provide enough context to read from the Web.Config from your application's solution.

How detailed into security is totally at your discretion. For most stuff, the above listed technique is fine, and seems to be the defacto standard best practice. If you really want to get even more secure (or borderling paranoia), I've seen one particular client store an encrypted string in the Web.Config, then have a separate decryption dll file that gets added to the file to decrypt the string, which then is used to pull a stored value from the registry (which was encrypted using a separate windows form application). The decrypted string pulled from the registry is the server connection string.

 

[Xenarchy]

I use a compiled dll to handle my encrypted connection and authentication to my database. This way even if the hacker gets into my server they still have to decompile my dll (which isn't THAT hard ) to get access to my database.

 

[mikeblas]

Why wouldn't you use integrated authentication to SQL Server, and let the service account take care of access?

 

[Xenarchy]

Why wouldn't you use integrated authentication to SQL Server, and let the service account take care of access?

Because not everyone wants to trust the machine next door to be the machine next door. If that is what you are getting at? Also that sort of service wouldn't work in a shared hosting environment.

 

[mikeblas]

Because not everyone wants to trust the machine next door to be the machine next door.

I'm sorry, but I don't understand that reasoning. How is trusting Windows authentication worse than trusting that nobody's managed to get a copy of your DLL, with your baked-in username and password?

 

[Zippeh]

Our reasoning was that since the SQL Server and the Web server are different machines, isn't it the case the Windows authentication doesn't go across different servers? This is what I was told when we programmed in ASP, and hence the need to use SQL server logins.

 

[PTNL]

Our reasoning was that since the SQL Server and the Web server are different machines, isn't it the case the Windows authentication doesn't go across different servers?

The Windows Authentication passing more depends upon whether the machines are allowed to communicate, such as being specified in the GP or networking hierarchy when both are in the same domain or physical/virtual location.

Regardless of whether you allow anonymous connections to the website or use Windows authentication of some sort (local username, domain account, etc.), it's a completely separate thing from the Sql Server logon. The Sql Server install can be setup to authenticate with the Windows domain, but most installs I've seen will stick with only allowing very limited local Sql Server user accounts. This logon separation can provide another layer of separation between the website and the database. Having a separate web server and database server is also recommended for high IO traffic and/or high bandwidth sites.

 

[mikeblas]

Our reasoning was that since the SQL Server and the Web server are different machines, isn't it the case the Windows authentication doesn't go across different servers? This is what I was told when we programmed in ASP, and hence the need to use SQL server logins.

No. Windows Authentication works for your whole domain.

You can use integrated security and completely avoid the security problem of baking sensitive information (like usernames and passwords!) into your connection strings or applications.

I can't understand why someone would ever not use integrated security in SQL Server. If there's a reason to do so, I'd like to learn it.

 

[Mercypoint]

No. Windows Authentication works for your whole domain.

You can use integrated security and completely avoid the security problem of baking sensitive information (like usernames and passwords!) into your connection strings or applications.

I can't understand why someone would ever not use integrated security in SQL Server. If there's a reason to do so, I'd like to learn it.

I think one of the headache with integrated security in SQL Server is that an user account has be to created somewhere in the domain.
In the enviornment where I work at, we deploy web applications that is used by thousands of different users. In one point of view, it might be more secure to have them all in the domain as users but the other point of view is why not just maintain that information in the database?
Well, it is easier to maintain user information and enforce different authentication methods if users are stored in the database and keeps the domain containing people who actually works there.
Back to the topic: If the web application intent is for internal use and everyone who logs in have an user account, then it will be best to use windows integrated security. If the scale is nation-wide, then I would rather put the connection string in a code/module file as a constant variable instead of web.config.

 

[mikeblas]

I think one of the headache with integrated security in SQL Server is that an user account has be to created somewhere in the domain.

It takes me less than a minute to create a domain account on my domain at home. Here at work, I can get an account created in less than an hour -- though it'll take a while for the account to propogate through the domain controller structure. (I can't imagine any company has a larger domain set than we do -- but maybe I'm wrong.)

In the enviornment where I work at, we deploy web applications that is used by thousands of different users. In one point of view, it might be more secure to have them all in the domain as users but the other point of view is why not just maintain that information in the database?

You can still use one logon in SQL Server for all those users, no problem. That lets you continue to use integrated security, and so on.

A simple way to do it (which might still have problems) it to grant access to the database for the anonymous user account that's hitting your web service.

Well, it is easier to maintain user information and enforce different authentication methods if users are stored in the database and keeps the domain containing people who actually works there.

I'm not sure why that's a goal. Your domain should contain security entities, not just "people who work there". If you look a little more carefully, you'll find machine accounts, plus other authority accounts (like administrator) who aren't necessarily a single person who "works there"; but are just accounts representative of a role.

If the scale is nation-wide, then I would rather put the connection string in a code/module file as a constant variable instead of web.config.

Why? Do you understand the vulnerability in that?

 

[Mercypoint]

It takes me less than a minute to create a domain account on my domain at home. Here at work, I can get an account created in less than an hour -- though it'll take a while for the account to propogate through the domain controller structure. (I can't imagine any company has a larger domain set than we do -- but maybe I'm wrong.)

You can still use one logon in SQL Server for all those users, no problem. That lets you continue to use integrated security, and so on.

A simple way to do it (which might still have problems) it to grant access to the database for the anonymous user account that's hitting your web service.

I'm not sure why that's a goal. Your domain should contain security entities, not just "people who work there". If you look a little more carefully, you'll find machine accounts, plus other authority accounts (like administrator) who aren't necessarily a single person who "works there"; but are just accounts representative of a role.

Why? Do you understand the vulnerability in that?

Unfortunately, Programmers here where I work at do not have the rights to create user account, that is handled by our IT department. Because of god knows what reasons (political...), it is done this way and we have no saying into this. I totally agrees with you that integrated security is the best way to go but sometimes, in different enviornment and companies, thing just don't flow that way.
I can give you an example of a web app I just worked on. This app has to do with HR information + some forms certain people have to fill out. So at the beginning, we were thinking it will be great if HR's PeopleSoft is linked with Active Directory so all information is centralize and we can deploy windows login so users don't have to remeber yet another password. But guess what? They can't get their act together so that is a no go. We can't wait until they finally decide to get their act together to deploy the app, so we have to tap into both PeopleSoft and AD to make it happen and sometimes the records don't even match! What a nightmare.... Anyways...I am just trying to present a different point of view in all of this.
I think we as programmers are here to support and satisfy the end users of our products. Sometimes, they like the fact that they will never have to change passwords again, or sometimes they want us to retrive their password without setting to a default password. I think all of this is easily accomplished with storing their user information in the database instead of integrated security. Another example is that most of the online bank and credit card account you access has features to retirve your password. I would bet they store all of their information in a database.
As for the connection string, .vb, .cs, web.config don't have to be deployed with your aspx files. If someone can log into your webserver and get all of those information from the files, then thats a totally different story...

 

[mikeblas]

I think we as programmers are here to support and satisfy the end users of our products.
Sometimes, they like the fact that they will never have to change passwords again, or sometimes they want us to retrive their password without setting to a default password. I think all of this is easily accomplished with storing their user information in the database instead of integrated security.

The use of integrated security between SQL Server and the web app host (and the middle-tier host, if there is one) isn't relevant to the users. They don't even have to know; they're not concerned with their passwords.

Another example is that most of the online bank and credit card account you access has features to retirve your password. I would bet they store all of their information in a database.

I think see the problem, now: I think you're confusing the application credentials with the database access credentials.

 

[BuddhistPunk]

Just a quick comment regarding using integrated security.

A year or so ago I was developing a system and used IS for SQL access and found it to be very slow in high load environments. Being an MS partner we spoke to them about it and they acknowledged this "flaw" exists in the SI system, but have no plans to fix it.

 

[mikeblas]

Just a quick comment regarding using integrated security.

A year or so ago I was developing a system and used IS for SQL access and found it to be very slow in high load environments. Being an MS partner we spoke to them about it and they acknowledged this "flaw" exists in the SI system, but have no plans to fix it.

Who was them? Microsoft? Who, specifically? I'd be interested in tracking down who you spoke to so I can understand their reasoning.

Is there a reason you weren't using connection pooling? How often were new logins created and resolved?

Anyway, I suppose that web-based applications where performance is more important than security exist, but they're not very common.

Let me stress that creating accounts only for "people who work there" is not the best solution. It's trivial to create an account that does not have interactive login priveleges, and that level of access is adequate for the connection between the scripts on the web server and the database back-end.

 

[Mercypoint]

Who was them? Microsoft? Who, specifically? I'd be interested in tracking down who you spoke to so I can understand their reasoning.

Is there a reason you weren't using connection pooling? How often were new logins created and resolved?

Anyway, I suppose that web-based applications where performance is more important than security exist, but they're not very common.

Let me stress that creating accounts only for "people who work there" is not the best solution. It's trivial to create an account that does not have interactive login priveleges, and that level of access is adequate for the connection between the scripts on the web server and the database back-end.

Cool.... Can you come over and work (read: kick some ass) with our IT staff so they would get off their butt and start creating and maintain those thousands of user account!

 

[[MS]]

The following guide is great for explaining how to secure your data access:
http://msdn.microsoft.com/library/en-us/dnnetsec/html/SecNetch12.asp

It explains why you would want to use NT authentication, and when it can\can't be used. It also provides a set of alternative that to securing the creds when NT autentication isn't an alternative.

Give it a read and it'll probably address the various questions.

 

[mikeblas]

Here's another one, which is like the article [MS] cites, but more from the SQL Server perspective: SQL Server 2000 SP3 Security Features and Best Practices: Secure Multi-tier Deployment

 

[mikeblas]

Here's another reference; this book looks very useful, and isn't expensive.Building Secure Microsoft ASP.NET Applications