Remote connection to SQL Server on LAN
-
Dear all,
I am trying to connect to a remote SQL server on another computer (connected through a router). I haven't done this yet but my connection string looks like below :-
Dim
connectionString As String = "server='(local)'; trusted_connection=true; database='BAS'"
I was wondering, to connect to the remote SQL Server 2000 on the other computer, do I just change the server parameter to 'COMPUTER_NAME/SQL_SERVER_NAME' ?
-
You can use the DNS name of the computer (mySQL.Mydomain.com) or it's IP address (10.1.1.1).
(local) is only when logged into the SQL Server computer, this is a built-in alias for the localhost or 127.0.0.1.
If there is a firewall between you and the SQL Server you will need port 1433 open to connect.
Create a test.udf file on your desktop and open it to tweak the settings until you can connect. Then open the UDF file using notepad and copy the connection string into your code.
Andy
-
Depending on the router you are connecting to/from you may also need to use port forwarding to ensure that it connects to the SQL server (this is of course unless your SQL server sits directly on a class C subnet). if you are not using a VPN - which it does not sound like you are then you also will struggle to use a trusted connection, the string I use is (please note the variable addins - this is such that it can be tested from various places updating as required):
sDBConnectionString = "data source=" &
sDBServer & ";initial catalog=" & sDBName & ";persist security info=False;workstation id=" sWorkstationID & ";user id=" & sDBUser & ";password=" & sDBPassword
-
Hey guys,
Thanks for the quick reply. I tried the UDL file connection thing but wasn't able to get it to work.
Do i need to change any setting in SQL Server to make sure that it can be accessed ? Like maybe to use SQL password instead of windows authentication ( I tried changing but it gives me an error saying that the login failed) ?
What are the necessary changes that I am missing here?
Thanks,
-Victor-
-
My apologies for the snide answer... it just seemed hard to believe that you were sitting on the answer and possibly had not tried it for ten hours. Thanks to those who responded seriously, as well.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
James' answer is what you should use for the connection string.
sDBConnectionString = "data source=" & sDBServer & ";initial catalog=" & sDBName & ";persist security info=False;workstation id=" sWorkstationID & ";user id=" & sDBUser & ";password=" & sDBPassword
Now there are variations depending on your network settings and your position in the network.
If you are part of the same NT domain trust as the sql server, you can replace user id and password by the following :
Intergrated Security=SSPI
Otherwise, you will need the user id and password parts to specify sql server authentication token. For that you should have changed the sql server configuration parameter to use Mixed Mode Security.
Another complication due to the network is the DNS (Domain Name Service). If both server and client (SQL Netwok Utility) are configured to use TCP (perferably), then your client machine has to be able to connect to the server by IP adress on a specific port (1433 by default, changeable from SQL Server Network Utility).
Try to ping the server machine by name. If it works, you can continue to use the name for the data source parameter. If not use the IP adress instead.
If the port is other than 1433 then you need to add the correct port to the IP adress, separted by comma (,).
HABIB.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply