Solved

Unable to connect ADP to sqlserver 2008

Posted on 2010-11-19
6
544 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 40

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

740 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