5

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.

yoozer8
  • 7,361
  • 7
  • 58
  • 93
Richard Reddy
  • 2,120
  • 3
  • 25
  • 39

2 Answers2

5

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
Raj More
  • 47,048
  • 33
  • 131
  • 198
  • Thanks for the feedback. Regarding impersonation do you know of any good guides or walkthroughs for setting that up? Cheers. – Richard Reddy Jun 25 '09 at 16:38
  • actually scratch that last question. Between your article and this question I have the info I need. Thanks. http://stackoverflow.com/questions/982216/database-connections-work-fine-when-application-is-run-from-localhost-login-fai – Richard Reddy Jun 25 '09 at 16:57
1

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.

Moose
  • 5,354
  • 3
  • 33
  • 46