Solved

Unable to connect ADP to sqlserver 2008

Posted on 2010-11-19
6
541 Views
Last Modified: 2012-05-10
Hi Guys,

Strange one this.... I've developed and Access ADP database which connects to an ISP hosted sqlserver and it connects and works remotely with no issues. Because it is exposed to the internet I've decided to go for a dedicated server so I can make it more secure etc.
I've got the server all setup with the database and I can connect to it remotely with sql server studio manager yet when I attempt to connect to it directly with my ADP database using the same credentials I get an error message ...

Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen(InvalidInstance()).]Invalid connection

Any suggestions as to why I'm getting this error message?

Thanks in advance for your participation.
0
Comment
Question by:dataflowjoe
  • 3
  • 2
6 Comments
 
LVL 84
ID: 34174185
Have you tried building a new test ADP file, and creating a new connection to the 2008 server (just to be sure you can do so)?
0
 
LVL 2

Author Comment

by:dataflowjoe
ID: 34174269
I've just done that in response to your question and I'm given the exact same message.
I'm wondering if drivers (ODBC)  are required on the server to enable a connection from an Access database? I've installed the latest MDAC drivers, not really sure if they are relevant to SQLServer

Keep the suggestions coming, I need this cracked!
0
 
LVL 39

Expert Comment

by:lcohan
ID: 34174410
Can you connect to the hosted SQL 2008 by IP in any way?  Do you have SQL Server management tools installed on the client where you test ADP file? If not you can at least install SQL 2008 driver

Installing SQL Server Native Client
http://msdn.microsoft.com/en-us/library/ms131321.aspx

More available at : http://www.microsoft.com/downloads/en/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en

Then you can check the following:

Do you need VPN to your hosted ISP where SQL is located?
local host file entry for that server name/IP
add an alias name for the SQL server name on your local client
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 84
ID: 34175933
Are your running management studio on your workstation? If not, and if you're running it on the server, make sure that you've enabled Remote Connections on the server.

Can you build a test .mdb/.accdb file and use linked tables to connect to the server? You'd have to build a DSN (Access has a utility for that) but it would tell you if it's the ADP that's giving trouble, or if it's something else.

AFAIK, you don't need to have anything installed on the server that wasn't installed during server installation. The drivers are installed on the client.
0
 
LVL 2

Accepted Solution

by:
dataflowjoe earned 0 total points
ID: 34276561
The reason for not being able to connect was because I didn't reference the full path to the server which is the IPAddress\InstanceName I just had the IP address which seems OK with SSSM but not with Access. As it is I've also gone fora fixed non standard port number to stop being bombarded with hacking bots. The full reference is [IPAddress or DSN]\[InstanceName],[PortNumber] example: 85.125.23.89\sqlexpress,5749

Thank you for your comments
0
 
LVL 2

Author Closing Comment

by:dataflowjoe
ID: 34308626
The reason for not being able to connect was because I didn't reference the full path to the server which is the IPAddress\InstanceName I just had the IP address which seems OK with SSSM but not with Access. As it is I've also gone fora fixed non standard port number to stop being bombarded with hacking bots. The full reference is [IPAddress or DSN]\[InstanceName],[PortNumber] example: 85.125.23.89\sqlexpress,5749

Thank you for your comments
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

815 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

8 Experts available now in Live!

Get 1:1 Help Now