ASP Page Cannot Connect to SQL Server-Not associated with a trusted co 3

[JohnBates]

hi experts,

I'm a DBA, trying to help a web developer connect to a SQL Server (2000) db using ASP 3 (not .net)

Error: "Login failed for user.... Not associated with a trusted connection."

NOTE: Server is set for 'Windows only' authentication
The SQL Server server is also the web server.


This is the asp code: (Server and database are same name "sonyBMG")

Do you see any defects in the connection string?

Thanks. John

<%
'sample SQL database connection syntax for ASP and SQL Server

Dim oConn, oRs
Dim qry, connectstr
Dim db_name, db_username, db_userpassword
Dim db_server

db_server = "sonyBMG"
db_name = "sonyBMG"

db_username = "OurDomainName\TheUserName"
db_userpassword = "SE.236.210"

fieldname="controlNum"
tablename="tblMain"

connectstr = "Driver={SQL Server}; SERVER=" & db_server & "; DATABASE=" & db_name &"; UID=" & db_username & "; PWD="& db_userpassword

response.write connectstr & "=connectionstr<br>"

Set oConn = server.CreateObject("ADODB.Connection")
oConn.Open connectstr

qry = "SELECT * FROM " & tablename

Set oRS = oConn.Execute(qry)

do until oRS.EOF
response.write ucase(fieldname) & ": " & oRs.fields(fieldname)
oRS.movenext

loop
oRS.close

set oRS = nothing
set oConn = nothing
%>




NOTE:

 

[SQLDenis]

I usually do stuff like this, you don't need user name and password for Integrated Security (Windows Authentication)

Set conn = CreateObject("ADODB.Connection")
dsn = "Provider=SQLOLEDB;DATABASE=DBName;SERVER=serverName; Integrated Security=SSPI;"
conn.Open dsn



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> Do you see any defects in the connection string?

I thought the whole idea about cr@ptive directory security is to map accounts there to SQL Server logins - without passwords and all that stuff.

That said... see

http://www.connectionstrings.com.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[JohnBates]

Thanks for the reply SQLDenis.

If we don't specify the user, how can we set permissions? The web user should only have access to the sonyBMG database.

To be able to specify user in the conn string, will the server have to be set to 'SQL Server Authentication'?
Then I would specify a *local* user I suppose...?

Thanks. John

 

[NoCoolHandle]

As per Denis..

His way is correct.. You can't (in asp) pass in a windows username and password. If you try, the error is what you got.

If you need to provide "specific NT account credentials" to an asp page, you need to force the "users" to "authenticate" with iis and allow impersination at the site level. Otherwise you will always connect to SQL as the level of the "ananomous user" (iis site config-> security)

It isn't too hard, the key is removing the everyone and iis accounts from the page/directory and then allowing just the specific users that you want to use the page permission to "read" the page.

HTH

Rob

 

[SQLDenis]

Cool link vongrunt, enjoy the purple star

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[JohnBates]

Thanks NoCoolHandle for the explanation. Thanks to everyone.

I may have to post back later. Things to consider.
John

 

[Nagrom]

I haven't tried this so I'm not sure if it would work, but you could try setting up an ODBC datasource that connects to the database using windows authentication, then specify that DSN with the UserID and Password. That might let you specify a different UserID than the web service. Just try creating an ODBC datasource called "DBName", then use the connection string:

"DSN=DBName;UID=TheUserName;PWD=ThePassword".

The drawback to that method being that, even if it works, you'd have to set up a DSN for each database you'd want to access.

 

[JohnBates]

ok I have more information now:

- The SQL Server is set for Windows Authentication

- IIS on this server is configured to allow Anonymous access
(The account used for anonymous access is IUSR_SONYBMG

- Integrated Windows authenticated is checked.

When I use this in the ASP :

Set oConn = CreateObject("ADODB.Connection")
dsn = "Provider=SQLOLEDB;DATABASE=SonyBMG;SERVER=SONYBMG; Integrated Security=SSPI;"
oConn.Open dsn

Get this error: Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user 'SONYBMG\IUSR_SONYBMG'.

So, by not supplying a user/password, it defaults to
'SONYBMG\IUSR_SONYBMG' and the login fails.

Do I need to create a SQL Server login for SONYBMG\IUSR_SONYBMG ?

Thanks. John

 

[JohnBates]

.... the fun never stops

After getting error Login failed for user 'SONYBMG\IUSR_SONYBMG' I created a SQL Server login for it, granting permissions to the SonyBMG database.

Now, when I run this:

Set oConn = CreateObject("ADODB.Connection")
dsn = "Provider=SQLOLEDB;DATABASE=SonyBMG;SERVER=SONYBMG; Integrated Security=SSPI;"
oConn.Open dsn

... get a different error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

This is supposed to be a DSN-less connection (I thought)
What is causing this error?

Thanks very much. John

 

[gmmastros]

Did you look at the link that Vongrunt pointed you to. It is extremely helpful. I suggest you modify your connection string, like so....

dsn = "Provider=SQLOLEDB;Data Source=[!]NameOfServer[/!];Initial Catalog=[!]NameOfDatabase[/!];Integrated Security=SSPI;"

It could just be coincidence, but... is the name of your server the same as the name of the database? It can be, sure, but it seems very coincidental.

This may not resolve your security issues, but hopefully this will help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JohnBates]

OK I've had a breakthrough.

(and thanks gmmastros - yes, db and server have same name-we are not very imaginative I suppose. More likely, we prefer to keep it simple

This is the conn string I used:
connectstr = "Provider=SQLOLEDB;DATABASE=SonyBMG;SERVER=SONYBMG; Integrated Security=SSPI;"

It only worked AFTER I created SQL Server login
SONYBMG\IUSR_SONYBMG giving it db-datareader, db_datawriter permissions to the db. Then everything fell into place.

Does this seem right?

**************Questions: ****************
1. Does IIS automatically chooses this default login (IUSR_SonyBMG) if you use the above connection string ?

2. Is this a good way to allow web users to access a SQl Server database? Any security concerns?

Thanks to everyone who contributed help, criticism or beer!

John

 

[NoCoolHandle]

1. Yes. The default naming is IUSR_ComputerName
2. I would be carefull about dbWriter permissions..
This opens you up to injection atacks. It is much better to create stored procs and assign execute permissions to the proc. No permissions on the tables (except select - maybe).
That way no mods can be done without a nice sanitizing stored proc.


my 1c

Rob