SQL Server Database Login Failed and Access Denied

Posted on 2007-07-24
Medium Priority
Last Modified: 2008-09-19
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!
Question by:tonqo

Expert Comment

ID: 19554381
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)

Author Comment

ID: 19554498
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")

Accepted Solution

sachin_raorane earned 1000 total points
ID: 19555117
As you are using windows authentication and IIS uses IUSR_CZC5350TBF for anonymous connection, so your SQL expect this user some permission on databases.

So either change the IIS authentication to windows, so user have to provide user name and password while accessing your website
Add IUSR_CZC5350TBF into SQL security section and give the database access to this user MS SQL

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Measuring Server's processing rate with a simple powershell command. The differences in processing rate also was recorded in different use-cases, when a server in free and busy states.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month13 days, 11 hours left to enroll

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question