ASP SQL Native Connection to SQL Express Server

I am trying to connect to a SQL database on another file server, but receive an error when I run the ASP:

XDBc Test

Microsoft SQL Native Client error '80004005'

Cannot open database "pbsdata" requested by the login. The login failed.

/odbc/test_xdbc.asp, line 10

The asp page is:

<h1>XDBc Test</h1>
<%
      'On Error Resume Next

      
      Dim conn

      set conn=Server.CreateObject("ADODB.Connection")
      conn.ConnectionString =  "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pbsdata;Data Source=profish-app01\SQLExpress"
      conn.open




      If conn.State = 1 Then
            response.write("connected1")
         Else
            response.write("FAIL")
      End If

       If db <> 0 Then

      End If
      conn.close
      set conn=nothing
%>


I had created the connection string by creating a udl file, and then cutting and pasting the connection string from notepad. When I test the UDL file, the test connection works.

The ASP page is located on a Windows 2003 Server Service Pack 2

Any help would be greatly appreciated.
Rick RudolphAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OCDanCommented:
You currently have it set to use integrated security, and the user that is passed through is not yours, it is the asp programs.

Just to verify this add a user and password to the end of the connection string one that you know has access rights on SQL server e.g.:
conn.ConnectionString =  "Provider=SQLNCLI.1;Persist Security Info=False;Initial Catalog=pbsdata;Data Source=profish-app01\SQLExpress;user=dan;password=dan"

If that is the case then check what user you have IIS set to run as, I believe the default is IUSR_MachineName

Although not this exact problem this article explains how to set appropriate security permissions:
SQL Permissions
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
As indicated, you would best advised to use SQL Server Authentication.
0
Rick RudolphAuthor Commented:
I never was able to make Microsoft Authentication work. SQL authentication did work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.