Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

SQL Server Problem

Hi,

I'm having problem connecting to the SQL server in my ASP.NET app.

I can setup the connection fine in the vs.net ide, the connection tests successfully, i can browse the tables etc.

However as soon as I try to access it thru the pages, it just throws up sql server does not exist / access denied on dbconn.open()

Anyone had similar problem and know the remedy?

Cheers

James

James 'Smiler' Farrer
Wednesday, August 06, 2003

As a followup, I can access the SQL server fine with classic asp on the same web server!

So this looks to be a .net specific problem with the setup somewhere. Anyone ideas?

James 'Smiler' Farrer
Wednesday, August 06, 2003

"...I can access the SQL server fine with classic asp on the same web server..."  -- Seeing as classic ASP connects using the IUSR_xxx account it seems that someone has already created a SQL Server Login for the IUSR_xxx account. ASP.NET runs under the ASPNET account, therefore you need to add a Login for the ASPNET account. Do this from SQL Enterprise Manager > Server > Security > Logins and while you're there, make sure you go to the Database Access tab and grant access to the right database(s) there.

Duncan Smart
Wednesday, August 06, 2003

Sorry maybe I made that a little unclear....

The SQL Server is on a different box and am authenticating it with a login.

So I don't see how an ASPNET account could come into it?

James

James 'Smiler' Farrer
Wednesday, August 06, 2003

Next update:

Added <identity impersonate="true"/> to the web.config file and it can now access the database... so I'm thinking the thing about that aspnet account must be right :)

Does this confirm your thinking?

James 'Smiler' Farrer
Wednesday, August 06, 2003

It sounds as if your application is trying to connect via a Trusted connection (ie. Windows authentication). Our preferred way of connecting to SQL Server is not via a Windows account but directly via a SQL Login. To enable SQL Authentication, SQL Server needs to be in Mixed authentication mode (not the default) - go to the properties of the server in Enterprise Manager go to the Security tab, and click the "SQL Server and Windows" authentication option.

Duncan Smart
Thursday, August 07, 2003

This book "Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication"
[1] (a free .PDF download or available at Amazon) goes through the numerous methods of setting up database authentication under ASP.NET.


[1] http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/secnetlpMSDN.asp

GuyIncognito
Thursday, August 07, 2003

*  Recent Topics

*  Fog Creek Home