SQL Server Performance

Connection string to use across a LAN

Discussion in 'Getting Started' started by Jonathan Trahair, Apr 8, 2008.

  1. Jonathan Trahair New Member

    Hi everybody
    I am trying to connect to SQL Server 2005 from another workstation on the network, from a VB6 app. I am developing. I have set the status of the SQL Server to accept remote access etc., as per previous posts (thank you).
    The connection string is:
    Driver={SQL Native Client};Server=computernamesqlexpress;Database=myDatabase;Trusted_Connection=yes;
    The error message is [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. It fails at .Open.
    I installed the ODBC driver as an attempt to connect somehow.
    The connection string works fine when running the app. on the same machine as the SQL installation.
    Thanks in advance.
    Jonathan Trahair
  2. jagblue New Member

  3. Jonathan Trahair New Member

    Still trying to connect across a LAN - the story so far.
    I have set the status of the SQL Server to accept remote access etc., as per previous posts (thank you). I have seen the web page with sample connection strings, again thank you.
    EITHER
    Driver={SQL Native Client};Server=computernamesqlexpress;Database=myDatabase;Trusted_Connection=yes; (the driver is MSDASQL).
    The connection string works fine when running the app. on the same machine as the SQL installation. On a remote machine the connection fails, error message is: 'datasource name not found and no default driver specified' So how can I specify a data source name, and install a default driver?
    OR
    I changed the connection string to:
    Provider=SQLNCLI;Server=computernamesqlexpress;Database=myDatabase;Trusted_Connection=yes;
    Again, works fine on local machine but not across LAN.
    Error message on network connection is 'provider cannot be found'. Where can I download a provider from?
    Thanks for your help.
    Jonathan Trahair
  4. jagblue New Member

    Hi,
    Just a question
    Is this machine is on the same network as sql server
    If not then will you able able to PING sql server from this machine?
    I know it's just a basic question but it matters a lot
    Thank You
  5. Jonathan Trahair New Member

    We connected eventually, thanks everyone. It was more complicated than simply choosing a connection string. My SQL Server is set for Windows authentication, so I could use trusted connections. The customer's was set to SQL Server authentication, so what worked on my machine didn't work on theirs. In fact, none of the connection strings worked on theirs - except
    "Provider=MSDASQL.1;Extended Properties="DSN=mydsnname;UID=sa;PWD=mypassword;" provided the user's local machine has such a dsn with that name and settings.
    In addition, I can't use that string on my system even with such a dsn, because my SQL Server is set to Windows authentication and doesn't like changing, and connection strings using dsn don't work on Windows authentication.
    So, in my program code I have to set the connection string according to whether the program is running on my laptop, or the customers' workstations.

    Thanks again.

Share This Page