Connecting to SQL 2000 remotely SQLOLEDB

Posted on 2007-10-12
Last Modified: 2010-05-18

This is driving me MAD.

I have a server  that I am trying to connect to, Win2003, SQL2000, IIS V6.
Now, I am able to connect to the SQL database Locally. However not remotely. Getting the following error:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
/includes/conn.asp, line 22
I have TCP/IP protocol enabled, no firewall enabled, default security MSDE/MSSQL permitted.

My connection string looks like this: see below; I am using the IP address on this remote connection, thats the only difference between this and the local connection that works, I have tried the local connection using the same IP and it works as well.  I think that this may not be my connection string but some other server setting that is not permitting remote connections?  

<% Option Explicit %>
  dim db_server,db_userid,db_password,db_name,conn

  db_server="**.***.***.**"                  'database server name
  db_userid="****"                        'Database User ID
  db_password="********"            'Database Password
  db_name="*******"            'Database Name

  dim conString
  set conn=Server.CreateObject("ADODB.Connection")

  conString="Provider=SQLOLEDB.1;Password=" & db_password & ";Persist Security Info=True;User ID=" & db_userid & ";Initial Catalog= " & db_name & ";Data Source=" & db_server

Question by:Tony-laptops
    LVL 7

    Expert Comment

    Try this
        Open a notepad at the web server and save it as test.udl. Now just double click the notepad to open the datalink properties and set the connection using the wizard and test the connection. Do not forget to select the OLEDB for SQL Server as provider.  If connecton is successful then you can just open the file in notepad and use the connection string.
    If on putting the IP address  on the server name and providing the user credential does not connect to the server then could be that the IP address is not accessible from the web server(try pinging) .
    LVL 7

    Expert Comment

    Correction - You need to double click the saved test.udl.

    Author Comment

    Ok, I got a similar error message:

    So that I make myself clear, I have 2 separate servers on 2 IP addresses.  I am trying to connect from server 1 to server 2.

    I have done the test from server 2 (test.udl) using server 1 IP address, got error:
    Test connection failed because of an error in initializing provider, DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

    When I try this on server 1 using server 1 IP, connection is OK.
    LVL 7

    Accepted Solution

    So that means server 1 is not able to communicate with server 2. So first you need to make sure that both servers can communicate. Did you ping server 2 from server 1 (Start > Run > ping Server2). If you get no reply then talk to your network guy (if there is one :) ) to make sure that Server 1 can communicate to server 2.

    Author Comment

    Hi Assyst,

    The host was restricting the connections. Basically, I needed to create a private LAN between the 2 servers (extra money of course) now its all working.

    Thanks for your help.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Suggested Solutions

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now