1

OK folks,

My company hosts quite a few websites. These websites all require a connection to SQL Server 2008.

When we deploy a site to a new server, we constantly have to modify the web.config to contain a connection string with the appropriate username and password. This requires the person deploying to setup a user for that database with all appropriate permissions, then modify the config file.

What I would like to do is, instead of creating a user for each database, create one user with permissions to every db and not specifiy a username and password in the config. This would save a lot of time, and I believe make it more secure (clients have access to the web.config via ftp, but many times we don't want them in the db).

Also, this must work where SQL Server exists outside of the current domain. It sounds like this may foil using Windows Integrated Security...

I have never used any setup other than specifying username and password (mapped windows domain user access). It sounds like I need to use Windows Integrated Security, but I can't find anything on MSDN for IIS 7.

Anyone's thoughts on security and setup would be appreciated.

Improve this question

2 Answers 2

Reset to default
2

Integrated Auth is what you are looking for, however this starts to get tricky when not in a domain. Especially when Windows 2008 is being used. It doesn't like Windows Auth between machines which aren't on a domain.

Assuming that you stick with Integrated Auth, you can setup each site to run in it's own application pool, and setup each Application Pool to run under its own account, so that the accounts don't have rights to the other databases.

If the customers have rights to FTP to the web server, they could upload files which would allow them to view other customers data, which would be a big no-no.

From a security standpoint making everything as a single account is a bad plan. An account per customer (either Windows or SQL) is a much better plan to use.

Improve this answer
0

You're on the right track. What you're looking for is Integrated Authentication. Grab a connection string for Integrated Auth from here: http://www.connectionstrings.com/sql-server-2008.

In IIS7, create a custom domain user for the app pool that your site runs under. For best security make sure that each site has its own app pool with custom user. Make sure to grant that user access to disk so that the site works. Then, grant that custom user access to the database too (using Windows auth). It's easier If you use a domain user although you can use local users as long as the username and password are identical on the IIS and SQL servers.

Essentially this means that the app pool user will be what connects to sql server rather than a specific username and password in the connection string.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.