SQL Connection StringRSS

19 replies

Last post Jun 16, 2006 08:37 AM by dalezjc

  • SQL Connection String

    Jun 06, 2006 08:59 AM|dalezjc|LINK

    I'm not sure if this is the right forum or not, but this is not an ASP.NET question per se, but a general connection question.

    I don't know if this is a unique problem, or I'm going about it the wrong way.  I currently connect to one of our SQL servers via a  priviliged account (by using RUNAS).  Works with no problem.  I now need the ability to connect to the same SQL server using ASP.  I have the following connect string, but I'm not sure how to specify the domain in the string, or is there some other way?

    <%
    Set demoConn = Server.CreateObject("ADODB.Connection")
    demoPath="DRIVER={SQL Server};" & _
    "SERVER=mysqlserver;UID=myusername;" & _
    "PWD=mypassword#;DATABASE=qdb"
    demoConn.open demoPath
    %>

  • Re: SQL Connection String

    Jun 06, 2006 10:24 AM|tomkmvp|LINK

    Why not UID=domain\myusername ?

    See also: http://www.aspfaq.com/show.asp?id=2126

    Tom Kaminski (former IIS MVP 2002-2010)
    http://mvp.support.microsoft.com/
  • Re: SQL Connection String

    Jun 07, 2006 11:42 AM|dalezjc|LINK

    Tried that, but I'm still getting:

     Microsoft OLE DB Provider for ODBC Drivers error '80004005'

    [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

    Other suggestions?

     

    Thanks!

  • Re: SQL Connection String

    Jun 07, 2006 03:15 PM|jeff@zina.com|LINK

    Have you Binged a solution before posting?
  • Re: SQL Connection String

    Jun 07, 2006 04:21 PM|dalezjc|LINK

    Been there, done that, got the t-shirt, but I'm not well-versed enough to make any of the examples work.  I'm missing something obvious, but it's beyond me.

    Thanks

  • Re: SQL Connection String

    Jun 07, 2006 11:01 PM|qbernard|LINK

    Is the server name 'mysqlserver' ? can you ping the server? etc?
    Cheers,
    Bernard Cheah
  • Re: SQL Connection String

    Jun 08, 2006 10:11 AM|dalezjc|LINK

    I can ping it just fine and log in using Query Analyzer as well.
  • Re: SQL Connection String

    Jun 08, 2006 04:13 PM|jeff@zina.com|LINK

    dalezjc

    Been there, done that, got the t-shirt, but I'm not well-versed enough to make any of the examples work.  I'm missing something obvious, but it's beyond me.

    Do you know how SQL is configured?  Do you know what version of SQL?  Can you connect with Enterprise Manager?  Do you have a SQL admin or network admin you can talk to?

    Jeff

    Have you Binged a solution before posting?
  • Re: SQL Connection String

    Jun 09, 2006 09:57 AM|dalezjc|LINK

    Yes, I can log in using Query Analyzer and Enterprise Manager.
  • Re: SQL Connection String

    Jun 09, 2006 11:35 AM|tomkmvp|LINK

    First off, make your connection string exactly like it's shown in http://www.aspfaq.com/show.asp?id=2126 (as previously posted).
    Tom Kaminski (former IIS MVP 2002-2010)
    http://mvp.support.microsoft.com/
  • Re: SQL Connection String

    Jun 09, 2006 03:48 PM|dalezjc|LINK

    Okay, here's my connect string now:

    <%

    demoPath="Provider=SQLOLEDB;" & _
    "Data Source=myserver;Integrated Security=SSPI;" & _
    "Initial Catalog=qdb"
    response.write demopath
    Set demoConn = CreateObject("ADODB.Connection")
    demoConn.open demoPath
    response.end

     %>

    But I'm still getting:

    DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

  • Re: SQL Connection String

    Jun 09, 2006 04:28 PM|jeff@zina.com|LINK

    What version of SQL Server?  For that connection string, you must have Windows Integrated authentication working on SQL for the account accessing SQL, either the logged in user or the anonymous access account on the web server.  There are issues with the anonymous account since it's a local account and not a domain account, plus you must have the correct security in SQL to match the account doing the access.  You must also of course have access to "myserver" from the web server.

    Check the SQL logs for a login attempt, you may get some info there.

    Jeff

    Have you Binged a solution before posting?
  • Re: SQL Connection String

    Jun 09, 2006 04:29 PM|jeff@zina.com|LINK

    One other thing, keep in mind that Enterprise Manager and other SQL tools run under the context of the logged in user, IIS and your app do not.  The logged in user isn't accessing SQL, IIS and your app are.

    Jeff

    Have you Binged a solution before posting?
  • Re: SQL Connection String

    Jun 13, 2006 08:55 AM|dalezjc|LINK

    Here's an update.  I'm using a new connection string:

    <%
    demoPath="Provider=SQLOLEDB;" & _
    "Data Source=myserver;Integrated Security=SSPI;" & _
    "Initial Catalog=qdb"
    response.write demopath
    Set demoConn = CreateObject("ADODB.Connection")
    demoConn.open demoPath
    response.end
     %>

    But I'm getting this error now:

    Provider=SQLOLEDB;Data Source=myserver1;Integrated Security=SSPI;Initial Catalog=qdb
    Microsoft OLE DB Provider for SQL Server error '80004005'

    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

    I also checked the SQL Server logs (from Enterprise Manager) and saw nothing.  I also checked the event logs and didn't see anything.

  • Re: SQL Connection String

    Jun 13, 2006 11:44 AM|tomkmvp|LINK

    Are you using Windows Integrated authentication?  If so, this is a delegation issue.  Try switching to Basic authentication.
    Tom Kaminski (former IIS MVP 2002-2010)
    http://mvp.support.microsoft.com/
  • Re: SQL Connection String

    Jun 14, 2006 01:16 PM|dalezjc|LINK

    So what account is being used?

  • Re: SQL Connection String

    Jun 14, 2006 04:00 PM|tomkmvp|LINK

    I don't know, you'll have to tell us.  If you allow anonymous access to the page, then IUSR.  If you require authentication then the logged on user.

    Either way, this is a common problem and then answer can be found at http://www.aspfaq.com/show.asp?id=2009

    Tom Kaminski (former IIS MVP 2002-2010)
    http://mvp.support.microsoft.com/
  • Re: SQL Connection String

    Jun 15, 2006 08:43 AM|dalezjc|LINK

    Sorry my question wasn't very clear.  Given that I'm using:

    "Data Source=myserver;Integrated Security=SSPI;" & _
    "Initial Catalog=qdb"

    How can I tell which account is being used to log in?  I can successfully log in to using Query Analyzer and Enterprise Manager (using runas).

  • Re: SQL Connection String

    Jun 15, 2006 11:34 PM|jeff@zina.com|LINK

    This will sound like an echo, but...

    We can't tell.  Are you using anonymous or is the user authenticating?  Did you read the suggested link, which resolves the issue?

    Jeff

    Have you Binged a solution before posting?
  • Re: SQL Connection String

    Jun 16, 2006 08:37 AM|dalezjc|LINK

    Yes, I read the link but I'm not getting it.  But you asked "is the user authenticating?".  How can I tell?