connection string for remote database server

Question

I have 2 servers setup at the moment. One is a web server running Win Server 08 and the other is a database server running SQL Server 08 on Win Server 08.

Currently I have my site setup so that it can read/write to the database by using a connection string I created with the database name, server ip, db user and db pwd. The db user I have created has a 'public' role setup in the database (not db owner) and can just run exec stored procedures. My the connection string currently looks like this:

<add name="SiteDBConn" connectionString="Server=IPOfServer;Database=DBname;User ID=userhere;Password=passhere;"/>

While this is working perfectly for me, I would like to setup a database connection string that did not contain any username and password. On some of my other servers, where SQL server resides on the same server as the web files, I am able to use a trusted connection and use the built in 'Network Service' user on my database. This lets me run a connection string with no username and password like so:

<add name="SiteDBConn" connectionString="Server=localhost;Database=DBname;Trusted_Connection=Yes;"/>

Is there an easy way to achieve a connection to the database without hardcoding a username and password - like the above connection string - when using 2 different servers? Am I wasting my time going down this route seeing as how the database user I created has only exec permissions anyways?

Thanks for your thoughts on this.

Answer 1

You are not wasting your time. This is a very good practice. When you separate out IIS and SQL on separate machines, here are some options:

• Create the asp.net user on both the IIS box and the SQL server (preferably with same password)
• Use impersonation (change the context your site runs under)
• Encrypt a connection string in the registry a config file and forget about trusted connections (brrrr)
• Switch the asp.net context to be a domain user
• Use IIS6 in native application mode

Answer 2

If the 2 servers are not in the same Windows domain, you cannot use trusted connection.

Also, the login must be created as a Windows authentication, you can't use a SQL authentication login with a trusted connection.

Otherwise, everything Tapori said.