connect to sql(MSDE) database error

[920506]

Hi,all
In my ODBC setup for SQL server, I used windows authentication. after set up, the test for datasource connection is successful.

In my ASP code, I used
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "dsn=CMC;Trusted_Connection=""sspi"""
it doesn't work, it always come up with error message.

Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'BETTYPC\IUSR_BETTYPC'.
/myweb/webmsde/init.asp, line 30

even I changed to
conn.Open "Driver={SQL Server};Server=BETTYPC;Database=CMC;Truested_Connection=Yes"

it still give me the same error message.
Anybody can help, please?
Betty

 

[amorous]

Try something like this:

For Standard Security

oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

For Trusted Connection security

oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Uid=;" & _
"Pwd="
' Or
oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Trusted_Connection=yes"

taken from the link

http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForSQLServer

hope this helps you

-VJ

 

[920506]

amorous,
I believe I used:conn.Open "Driver={SQL Server};Server=BETTYPC;Database=CMC;Truested_Connection=Yes"

which is the exactly same as you suggested:
oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Trusted_Connection=yes"

If my windows login username is Betty, do I need to
run command in query analyzer:
sp_addlogin 'Betty', 'password'
in order for ASP user database? anything else like IIS setup I need to do??
Thanks
Betty

 

[mike2312]

Connecting IIS to a SQL Server (MSDE) requires that the anonymous IUSR account be granted access to the database\tables. This is easily performed using SQL Server enterprise manager.

sp_addlogin will create a user/password for a SQL Server using SQL Server Authentication.

When you use the "trusted=Yes" part of a connection string you are using windows authentication.

If you do not have EM you will be adding this user to your SQL server using sp_grantdbaccess. Check out Sql Server Books Online for the syntax.

 

[920506]

hi, mike,
thanks for your valuable posting. I used EM and your suggestion,I added login 'BETTY\IUSER_BETTYPC' and grant access to default to CMC database, now it doesn't complain with that message anymore.

but first I tried to use the following command, it doesn't work in query analyzer.
I typed
use master
sp_addlogin 'BETTY\IUSER_BETTYPC'
go
--it complain, '\' is not a valid character.
the I typed
use CMC --database name
sp_grantdbaccess 'IUSER_BETTYPC'
go
thanks
Betty