SQL Server Database Login Failed and Access Denied

Posted on 2007-07-24
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
    LVL 3

    Expert Comment

    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

    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")
    LVL 5

    Accepted Solution

    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

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Creating and Managing Databases with phpMyAdmin in cPanel.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now