Help needed with SQL Server authentication

[HowardMarks]

Dear All

I wonder if anyone could help guide me through a problem with connecting to sql server through an ASP page. I am pretty much a novice when it comes to SQL server administration and I can't fathom this out to save my life.

Here's the setup we have, and what SHOULD happen

one server, WS04, running IIS and serving the intranet pages
another server WS05, running SQL server database for intranet.

Users connect to the intranet, using integrated Windows authentication to identify them with their Active Directory account. No problems with this step.

WS04 should then connect to the database on WS05 using a system DSN on WS04 with a single SQL server account regardless of which user is logged in. Here's where the problem is.

If I choose windows authentication for the database, it works, but this means adding in SQL accounts for every Active directory user, which we don't want to do.
However, if I use basic authentication, using an account called 'intranet' which I have set up for the database, i get

Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'intranet'.

I am guessing this may be something to do with a clash between using the windows authentication against IIS but basic authentication against SQL Server? I've tried pretty much everything I can think of but my lack of experience is showing pretty badly.

Can anyone point me in the right direction?

Thanks in advance

Nick (Webmaster)

http://www.npfx.com

http://www.artswales.org

 

[SQLBill]

Check the SQL Server to see what authentication mode it is set for. It's probably set for Windows Authentication Only.

One solution, why not create a Windows group account and put everyone into that group? Then give that group permission to SQL Server?

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

Also make sure that the intranet account in SQL has the correct account, is enabled, and that the policy check boxes are unchecked in SQL for that account.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com