Error with Integrated Security & SQL

[lbob]

Hi

I'm an ASP.NET newbie and am having a few problems connecting to our SQL Database.

I have got a really basic ASP.NET page that is connecting to a database and returning a list of users. The IIS is on my PC and I'm trying to connect to one of our SQL Servers on another box. I've updated the security setting in IIS to be windows integrated security. The SQL Server currently only accepts Windows Security. However I'm getting the following error...

"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. "

The code i'm using is...

Dim conn As New SqlClient.SqlConnection _
("data source=xxxxxxxx; integrated security=SSPI;" & _
"trusted_connection=yes; initial catalog=xxxx")

Dim cmd As New SqlClient.SqlCommand("Select * from users", conn)

conn.Open()

Dim dr As SqlClient.SqlDataReader
dr = cmd.ExecuteReader
Do While dr.Read
Label1.Text &= dr("first_name") & " " & dr("last_name") & ", "
Loop

dr.Close()
conn.Close()

If I copy the same code into a new Windows Application and run it, it works fine?!

Please help! Thanks in advance
Lbob

[wey97]

You CANNOT have IIS and SQL Server on different computers using Windows security.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbconaccessingsqlserverfromwebapplication.asp

I don't see how this could ever work for you.

[lbob]

Oh ok, so a complete change from existing ASP where you can access a SQL Server using Integrated security from a different server!?!

[Fishcake]

Had a read of the article out of interest and found this
If your application runs on a Windows-based intranet, you might be able to use Windows integrated security for database access. Integrated security requires:

* That SQL Server be running on the same computer as IIS.
* That all application be users on the same domain so that their credentials are available to the Web server. (That is, Windows integrated security is not practical for a public Web site.) That for any web application not just asp.net.

Makes sense to me, but i've never used integrated security so not much help i'm afraid.

[lbob]

I still don't understand why, because we currently run ASP from another server to our SQL?!
I've read something about setting impersonate = true in the web config
Any ideas?

[wey97]

For that to work you must be passing a username and password. I don't see how it could work with integrated security.

[lbob]

But why would change the current functionality in ASP to ASP.NET ?

[wey97]

Originally posted by lbob
But why would change the current functionality in ASP to ASP.NET ?
I don't understand your question.

[lbob]

Currently you can use integrated security where IIS is NOT on the same box as the SQL Server, therefore, I don't understand why this shouldn't continue in .NET

[nemaroller]

I don't think any of us have a definitive answer...

but the .Net framework has a much tighter and strict security model, and simply because a ASP app connects to a SQL DB, doesn't necessarily mean a .NEt asp app will.
ASP.NEt uses a user account on the local machine (sometimes 'Machine', sometimes 'ASPNET'), that obviously isn't recognized across machines.

That said, you can impersonate a user, but considering the steps involved (the most ardious being configuring the local ASP account), why not ask the SQL DBA if he can set up exec permissions on a stored proc for a new user account you will use in your app.



If you want to try the impersonation route, here's the info:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskaccessingsqlserverusingmappedwindowsdomainuser.asp



To set user permissions for the Windows domain user

Using Windows administrative tools on the Web server computer, make sure that the mapped Windows domain user has necessary privileges. For details, see ASP.NET Required Access Control Lists (ACLs).
Mapping to the Windows User and Enabling Impersonation
After establishing the correct permissions for the ASPNET or NETWORK SERVICE user account and for the user to map to, you configure the application to impersonate that user.

To configure your Web application for integrated security

Open the Web.config file for your application and add the following elements:
<authentication mode="Windows" />
<identity impersonate="true" userName="domain\username" password="password"/>
The <authentication> element might already be there.

Note Elements in Web.config are case sensitive.
Using Windows Security in the Connection String
Finally, when you create connection strings for database access, configure them to use Windows integrated security.

To use Windows security in the connection string

When you create a connection string for your application, do not include a user name and password. Instead, set the connection string's Integrated Security attribute to SSPI.
The following example shows a connection string that includes the appropriate attributes:

data source=myserver;initial catalog=northwind;Integrated Security=SSPI
To configure SQL Server for integrated security

From the Windows Start menu, choose Microsoft SQL Server, and then choose Enterprise Manager.
Open the node for the server and expand the node for the database you want to give users permissions for.
Right-click the Users node and choose New Database User.
In the Database User Properties dialog box, enter domain\username in the Login name box, and then click OK.