Problem with ConnectionString

[Jay1b]

Hi

I've created an Intranet site, the site references a SQL Server. If I use the following connectionstring it works:

	<connectionStrings>
		<add name="Conn" connectionString="Data Source=SQLINSTANCENAME\DATABASENAME;Initial Catalog=ADL_Internal;user id=sa;password=PASSWORD" providerName="System.Data.SqlClient"/>
     </connectionStrings>

However this is clearly unsecure! Internal VB.NET applications work with the following connection string, but when i try this connection string with the ASP.NET site, it doesnt.

	<connectionStrings>
    <add name="Conn" connectionString="Data Source=SQLINSTANCENAME\DATABASENAME;Initial Catalog=ADL_Internal;Integrated Security=TRUE;" providerName="System.Data.SqlClient"/>
  </connectionStrings>

This is the error i get:

Login failed for user SQLINSTANCENAME\DATABASENAME$'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user SQLINSTANCENAME\DATABASENAME$'.


On another webserver, we have classic ASP pages, using Integrated Security which works find. What do i do need to do to get it working for my ASP.NET page? The IIS setup is the same as far as I can tell.

I hope this post isnt a bit to 'muddled', I would appreciate any help you may be able to give.

Thanks

[si_the_geek]

Using the "sa" login at all is not a wise move, and should always be avoided (especially from applications).

By using "Integrated Security" you are using Windows login details, so whichever Windows account is running your application needs to have permission to the database.

I'm not sure what account ASP.Net runs as, but ASP used something like IIS_USR. If you look at the log in SQL Server (in the "Security" section of Enterprise Manager/Management Studio) you should be able to see which user(s) failed to login, and hopefully it will be clear which you need to give permissions to.

[Jay1b]

Thanks for replying. I dont need to use the sa login for applications and wouldnt dream of it as it can be revealed from the app.config.

Our company network uses Active Directory, we log onto the network via the domain.

Our classic ASP webserver (intranet) is hosted on the actual db server. Using the connection string mentioned before (the one without the sa login) works fine for this.

The ASP.NET server needs to pass its details over to the DB server, i guessed this would be via Integrated Security. Could this be the problem? Could the db server be looking for the webserver user name in AD, not finding it and then throwing up the error?

Actually from reading what you've written again, thats pretty much what you've said lol... thanks

[si_the_geek]

It's actually the database that can't find the username, rather than AD (but potentially AD could cause an issue too).

You need to create a Login inside SQL Server (which you can connect to the relevant Windows login), and give it apt permissions for the database (preferably by using an SQL Server group, if you have them set up already).

[mendhak]

Assuming you have windows authentication checked in IIS, you need to set <identity impersonate="true"> in your web.config so that the application runs under the security context of the logged in user rather than the ASPNET user or NETWORK SERVICE user.