Not associated with a trusted SQL Server connection 1

[benlinkknilneb]

Hi all,

I'm brand-new to programming for SQL Server, after having started in Access. I decided after installing Visual Studio .NET 2003 that I would run through some of the tutorials for ASP.NET. I followed the directions to the letter for creating the web form and every time I run it I get this error message. I have been testing it using an account that has administrative privileges on the server as well as on the local machine (it's in my "sandbox"). Any idea how I can get around this?

Ben
The ships hung in the sky in much the same way that bricks don't. - Douglas Adams

 

[Jamfool]

have you added the user to the sql server?, and not just on the operating system.

 

[benlinkknilneb]

Yes. The Data Connection object is set for Windows Authentication and the user account that I'm logged into on this PC has all permissions but dbo on the server.

Ben
The ships hung in the sky in much the same way that bricks don't. - Douglas Adams

 

[Jamfool]

your server i guess is in mixed mode.

can you create an odbc that connects?

is the sql server installed in the default instance?

 

[benlinkknilneb]

My server is set in Windows authentication, not mixed mode. The problem occurs when it tries to open the SQLConnection object from ASP. When I press the "test connection" button in the SQL Connection wizard, it says that it passed the test... but the username that it seems to be passing to the system is (null).

Ben
The ships hung in the sky in much the same way that bricks don't. - Douglas Adams

 

[mrdenny]

What authentication method are you using on the web server?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[benlinkknilneb]

mrdenny,

Do you mean in the web app, like inside the DataConnection object? It's set to Windows authentication just like the server is.

If you mean on the web server itself, it's actually a sandbox running on my local PC, so I should have full rights to it, too. I can see other html pages on this web server with no problems.

Ben
The ships hung in the sky in much the same way that bricks don't. - Douglas Adams

 

[mrdenny]

If you are connecting to the web server via Anonymous access on the Directory Security tab of the IIS settings for the web site then it is trying to connect to the SQL Server via the local IUSR account that the web server is using to login.

As this isn't a domain account the SQL Server can't authenticate it.

Set the IIS server setup to Basic Authentication and it will be able to pass the username and password to the SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[benlinkknilneb]

Yes, but if I do that, then the system loses some of its security. If it authenticates against the Windows username and password (since my SQL Server isn't in mixed mode), then I'm sending everyone's Windows passwords out plain-text. That's just begging for trouble... or does it do something I didn't know about?

Ben
The ships hung in the sky in much the same way that bricks don't. - Douglas Adams

 

[SQLBill]

To use Windows Authentication with SQL Server you must:

1. everyone accessing SQL Server MUST have a SQL Server login set for Windows Authentication. This login MUST be the same one they use for Windows.

2. the client and SQL Server computers MUST be a) on the same domain OR b) on trusted domains. Trusted domains use common Domain Controllers for authenticating logins.

-SQLBill

Posting advice: FAQ481-4875

 

[benlinkknilneb]

SQLBill,

Both of those statements are true.

My problem is that I am creating an ASP.NET page and it's trying to pull data from the server. Denny said that I should use Basic Authentication in IIS... but if I do that, then the passwords are sent as plain-text. While this is for an intra-net page, which won't be visible outside of the physical campus, I don't want people's Windows passwords flying around in plain text. It wouldn't take much at all to see them and poof! Suddenly someone can have the vice president of the company's windows account information.

How do I set up IIS and SQL Server so that my ASP.NET pages will authenticate with their Windows accounts, but with at least a little bit of security?

Ben
The ships hung in the sky in much the same way that bricks don't. - Douglas Adams

 

[mrdenny]

If you want to setup so that the user logs into the SQL server with there account you have to use Basic Authentication. Integrated authentication will not work correctly. To do this securly you'll need to setup a certificate.

The reason that it doesn't work is called the tripple hop rule on NTLM authentication. Here is the basics of why it won't work. A machine can not pass on an authentication request that it has received from another machine to a third machine.

With Integrated Auth this needs to happen.
User logs onto there workstations.
Workstation passes the authentication onto the Web server.
The web server tried to pass the authentication onto the database server but can not because the database server knows that the web server didn't actually authenticate the user so the login request is rejected.

With Basic Auth this is how is happens.
The user is prompted to log into the web server via the popup. The web server is now the first computer in the chain. When it passes the authentication to the SQL Server the SQL Server knows that the login request is valid as the machine passing the login request to it was the one that checked against the domain controller.

The resolutions that you can use are:
Put IIS and SQL on the same server.
Use Basic Auth
Have the anonymous user account that IIS is using be the account that logs into the SQL Server

And here is the MS KB doc that confirms all this.

http://support.microsoft.com/default.aspx?scid=kb;en-us;176377

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[benlinkknilneb]

Ok, mrdenny, I think I'm following you on this, but I've got one more question:

If IIS and SQL are on the same server, and I'm using Basic authentication, and the user tries to access a view that uses a table/column that their personal SQL ID doesn't have permission to see... do they get blocked, or do they get in?

The reason I was wondering is that you said

Have the anonymous user account that IIS is using be the account that logs into the SQL Server

I still need SQL to know the difference between the permission levels of users jsmith and bjones. Are they logging into SQL server as the same userid with what you've specified above, or do they log into SQL with their jsmith/******** combo that they use to get into Windows?

Ben
The ships hung in the sky in much the same way that bricks don't. - Douglas Adams

 

[benlinkknilneb]

I read it again and I think I understand:

They're logging in as "anonymous" to the WEB SERVER and they're then being asked to authenticate with their Windows ID on SQL Server. Did I understand it right?

Ben
The ships hung in the sky in much the same way that bricks don't. - Douglas Adams

 

[mrdenny]

No, you are getting it confussed.

If they log into the Web server as "anonymous" then all users log into the SQL Server with the same account (the one that IIS is setup to use as the Anonymous account).

If a requirement is to have the web server log into the SQL Server with the users NT account you have a couple of options.

1. Have the IIS and SQL server on the same server
2. Use basic authentication on the web server along with an SSL cert between the browser and the web server.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[benlinkknilneb]

Ok, I get it now. I'll probably try to go with having them both on the same server. Since it's purely in-house, I shouldn't have too much trouble with traffic being too much for one server to handle both. Thanks a million for clearing that up for me!

Ben
The ships hung in the sky in much the same way that bricks don't. - Douglas Adams

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com