Connect to SQL Server from A2k

  • Thread starter Thread starter Anand
  • Start date Start date
A

Anand

Hi,
I am trying to use an A2k ADP with SQL Server 2005 installed on a remote
machine. I need to know:

Is it possible to connect to SQL Server 2005 through A2k or do I need a
higher version of Access

If it is possible, then why am I getting a 'Server does not exist/access
denied' error message in the Data Link properties. Following configs are made:
In SQL server Windows Authentication is enabled. My win Login account is
added for Server and DB login. Server is configured to accept remote
connections thru TCP/IP and named pipes. I have admin access to the LAN.

The data link page shows only name of the server machine and not the SQL
server. Shows only ABHATTA in the drop down list whereas the server name
appears as ABHATTA\SQLEXPRESS when I login at the Server machine. Server
machine runs Win vista.

Am I missing something here?

TIA

Anand
 
For connecting to a remote machine, usually you cannot use a windows Login
account to connect; so you should use a sql-server login account instead.
(Technically, it's possible to use a Login account for remote access but
it's more or less difficult to configure this and I won't suggest/recommand
it because it's unstable.).

Using a sql-login account require the SQL-Server Mixed Mode Authentication
mode enabled instead of the exclusive Windows Authentication mode. You must
also create a sql-server login account.

You must use ABHATTA\SQLEXPRESS as the server address.

Also, it's very likely that you'll be blocked by the firewall: make sure
that the port 1433/1434 are opened. As it's a named instance, SQL-Server
will use another port (dynamically allocated) to connect; this will be
permitted by some firewall and not by others; so you might have to
reconfigure the instance to use a fixed port instead and open it through the
firewall.

Finally, I don't suggest to use ADP 2000 as this version is full of bugs.
Use ADP2003 instead as the first choice or ADP 2007 as the second choice.

--
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)
 
Hi Sylvain,
Thanks for the inputs. Have tried the following but with no success:
-Have enabled multi mode security in the SQL server.
-Have set SQL Server to listen on fixed port 49172 through the TCP settings
of the Server config manager. The event log on Windows mentions SQL Server is
listening on TCP port 49172. Have punched a hole in the Server Firewall for
the said port.

-User sa configured in SQL Server with a blank pwd.
-Can connect to the Server through Access FE on the server machine. For the
local machine both Win Integrated and SQL security is able to establish the
connection.

-With a remote computer the same settings as in the server machine results
in the Access Denied/Server does not exist error. I also dont see the
Abhatta\SQLExpress name of the server in the Datalink page of the remote
machine - I just see the Abhatta name.

I tried to browse to the location http://192.168.0.221:49172 through
Internet Explorer but get a webpage not found error.
My LAN has a proxy server enabled for Internet access. Could this be causing
the problem. Can a A2k ADP connect at all to SQL Server 2005?

Can you pls help.

TIA
Anand.
 
Update on my following post:
I can establish the connection remotely if I turn Off (temporarily) the
Firewall on the server machine.

Anand
 
Well, if you can connect by turning Off the Firewall, then there is it;
although I'm not sure what you mean with "temporarily". You should add the
port address after the server address (without the instance name?),
separated by a comma (",") and not by two points (":"). You can also add
the prefix tcp: before the address.

A good idea would be to define an alias for the SQL-Server (SQL Server
Configuration Manager | SQL Native Client Configuration | Aliases) and use
it instead.

Also, I don't understand why you try to use the address 192.168.0.221; as
this is not a remote address. Are you on a VPN?

--
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)
 
Back
Top