asked on

SQL Server Database Login Failed and Access Denied

I am struggling with accessing a database from an ASP page. The database is residing on the local machine and I have setup the IIS server and tested that its working.

I have setup the data source as DSN=local; Server=(local) and Database=SAB_Acronyms

I don't know what authentication I have to used on DSN Configuration as well as Server Registration properties. Currently I have set both to Use Windows Authentication and I have set the connection as follows on the asp page:
strConnection = "DSN=LOCAL;Database=SAB_Acronyms;Server=(LOCAL)"
conn.Open strConnection

When I call the page with no username and password specified, I get an error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'CZC5350TBF\IUSR_CZC5350TBF'.

However, if I call the page with my Windows Username and password included in the connection String like: strConnection = "DSN=LOCAL;Database=SAB_Acronyms;Server=(LOCAL)"
strConnection = strConnection & "User ID=user1 ;Password=pass"
, I get a different error:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]SQL Server does not exist or access denied.

Please advise!

Askeeto

To connect to a local sql server with trusted security it is best to use a SQLOLEDB provider. This can be done by using the connectionstring below

strConnection = "Provider=sqloledb;Data Source=(local);Initial Catalog=SAB_Acronyms;Integrated Security=SSPI;"

When you use trusted connections you should not specify a username and password. The SQLOLEDB provider will automatically trust the credentials of the user that is executing the code. (in this case your asp user)

tonqo

ASKER

I still get an error for Login Failure. My code mow looks as follows:

dim conn,objRS,x
Dim strConnection
Set conn = Server.CreateObject("ADODB.Connection")
strConnection = "Provider=sqloledb;Data Source=(local);Initial Catalog=SAB_Acronyms;Integrated Security=SSPI;"
' strConnection = "DSN=LOCAL;Database=SAB_Acronyms;Server=(LOCAL)"
' strConnection = strConnection & "User ID=user1;Password=pass"
' Response.write(strConnection)
conn.Open strConnection
set objRS=Server.CreateObject("ADODB.Recordset")

ASKER CERTIFIED SOLUTION

sachin_raorane

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial