Access 2007 ADP project will not connect to SQL Server 2008

[Chris Woodard]

Ok, let me get the technical specs out of the way:

I have SQL Server 2008 on one server on my network. I configured a specific port, punched it through the firewall, and I can connect to this server using ODBC or SQL Management Studio using the following info: tcp:<servername>,tcp port. This works fine with these two.

The issue is when I try to connect with an Access 2008 ADP. When I put the server name in as tcp:<servername>,tcp port I get "dbnetlib sql server does not exist or access denied". Can anyone tell me why? Is this just because an ADP cannot connect this way? I am trying to keep this as secure as possible and am trying to do this without using the SQL Browser. Is the SQL Browser a must in this case?

Any help will be very much appreciated.

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx

 

[Sylvain Lafontaine]

First, double check that you didn't make any error about the servername and
its port. If this doesn't work, try using its tcp address directly instead
of its network name. This last point is often important if your SQL-Server
is running on a cluster. If this doesn't work either, then create an alias
in the client's SQL Server Configuration Manager and use it instead.

If this still doesn't work, then probably that you'll have to take a deeper
look at your firewall.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Chris Woodard]

Ok, I got this connection to work by turning on the SQL Server Browser. Is
this a necessity? Everything seemed appropriate without the browser using
tcp:<server>,Port. I just could not understand why I could connect via
Management Studio from another PC without the browser, but could not connect
via Access without the browser. I figure it has something to do with the
ADPs way off connecting verses Management Studio or a simple DSN.

Thanks for the help and if you have any insight on this, please throw it my
way.

Chris.