sa login failure in DTS

  • Hi All:

    I installed SS2K (as domain admin) and created a simple DTS package using ADO

    to return the number of records in a specific table meeting a condition.

    When I try to run the pkg, I get an error stating SQL Server cannot be found or access is denied on the line of code that attempts the connection to SS.

    This happens regardless of the id and pwd combination I try, including sa.

    I can query and manipulate the entire db using QA, but DTS appears to be operating differently.

    Any help appreciated.


  • quote:

    created a simple DTS package using ADO

    Can you post the connection string, with replacements for what you are using for userid (e.g.,MyUserName) and password (MyPassword)? Let's make sure it's not an ADO connection string issue.

    K. Brian Kelley

    K. Brian Kelley

  • Thanks for responding -

    I copied the connect string from another (working) DTS pkg on a different machine/network. Here's the connect string:

    strConnect = "Provider=sqloledb;Data Source=myserver;Initial Catalog=mydatabase;User Id=sa;Password=;"

    When I attempt to run the pkg, I'm logged in as the network domain admin, not the best practice but it is a lab setting.



  • Looks like you are using SQL auth. Is the server set for SQL Auth? or NT only?

    Steve Jones

  • How are you establishing the ado connection, trusted or sql login? I'd double check that line. Maybe post code here so we can see.


  • The Authentication was set to 'Windows only', the startup service account set to 'System account', which is how I installed SS2K (Developer Edition).

    I tried changing the Authentication mode to SQL Server and Windows, cycled SQL Server and the Agent account, and tried again. Exact same result.

    Here are the actual error particulars:

    Error Code: 0

    Error Source = Micorsoft OLE DB Provider for SQL Server

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

    The DBNETLIB bit seems odd. I'm running TCP/IP and Named Pipes, and everything is on the same (W2K Pro sp2) box. It almost sounds like the DTS pkg can't see the (SQL) server.


  • I think you might have a problem relating to the package owner. You don't need DTS to use ADO - you could test it using a simple .vbs file.


  • Can you post your ADO code? Let's look at that to make sure there aren't any issues.

    K. Brian Kelley

    K. Brian Kelley

  • Ok, I got it.

    My connect string had the SQL Server name, but not the host server name. In my case they are the same (probably not a good idea)

    so ADO/ODBC couldn't resolve the (SQL) server name correctly. I added the host server name to the connect string and it now works in VBS and DTS.


    strConnect = "Provider=sqloledb;Data Source=Chicago;Initial Catalog=myDB;User Id=sa;Password=;"


    strConnect = "Provider=sqloledb;Data Source=Chicago\Chicago;Initial Catalog=myDB;User Id=sa;Password=;"

    The light went on when I took Andy's suggestion and tried the code as straight VBScript and got the same error.

    Thanks to both of you!


  • Glad it worked and thanks for the followup.

    Steve Jones

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply