For one of my client, I have recently migrated SQL Server 2005 database to a vertual environment provided by Citrix. Access ADP application has to connect to SQL Server 2005 over the internet. Can somebody give tips how I can accomplish this. 
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Connecting to SQL Server over internet on port 80 (1 Viewer)
- Thread starter ak_ls
- Start date
Hmmm, I personally wouldn't want to use port 80 as that's usually the port web server listens on. If you don't want to use SQL Server's default 1433 you can use another port that's more obscure. Just has to be in range of 1024 to 65535.
But- I'm also unsure if this is even relevant because if you're using Citrix, then Access would be running on the Citrix server so you only need to compare the Citrix server's location in realtions to the SQL server and more likely than not, they would be within same network so there's no special need for connecting via WAN.
Or maybe I'm not completely understanding what your network topology is like.
But- I'm also unsure if this is even relevant because if you're using Citrix, then Access would be running on the Citrix server so you only need to compare the Citrix server's location in realtions to the SQL server and more likely than not, they would be within same network so there's no special need for connecting via WAN.
Or maybe I'm not completely understanding what your network topology is like.
Banana,
The network where SQL Server 2005 is installed and the networks to which users are connected do not trust. There is a need to provide access to teams connecting through networked that do not trust each other.
The network where SQL Server 2005 is installed and the networks to which users are connected do not trust. There is a need to provide access to teams connecting through networked that do not trust each other.
Hmm, I'm sorry but that didn't really help me understand the situation any better.
It's still the Citrix's job to provide access to the network resource, one of them being SQL Server.
Let's try this way:
Is your Citrix server in the same network as the server hosting the SQL Server? If so, then your users only will need access to Citrix server and Citrix will handle the communication with SQL Server. There would be no need for WAN connection to the SQL Server.
It's still the Citrix's job to provide access to the network resource, one of them being SQL Server.
Let's try this way:
Is your Citrix server in the same network as the server hosting the SQL Server? If so, then your users only will need access to Citrix server and Citrix will handle the communication with SQL Server. There would be no need for WAN connection to the SQL Server.
Citrix and SQL Server are on the same network. Users are on different networks, in different companies, in different countries and are not allowed remote desktop to gain access to Citrix. Access ADP application has been successfully tested in Citrix through remote desktop logon only.
Well, the first thing is that you need to have your SQL Server publicly accessible to the world.
I assume you of course understand the security ramifications and will take care of this one way or other.
Anyway, you simply need to know what your SQL Server's public IP is, and open a port in the firewall surrounding your server. Could be the default 1433 but it's good idea to obscure it and use a random port between 1024 to 65535.
Then with the SS authentication, which I assume either you will supply via your code or your user will fill it in just in time, you can construct a connection string. I usually would use DSN-less connection (you can google for Doug J. Steele's VBA sample on this) so I don't have to show the users the default DSN administrator dialogs. If you need the syntax for the connection strings, go to Carl Prothman's site for connection strings.
I hope that will help get you started.
I assume you of course understand the security ramifications and will take care of this one way or other.
Anyway, you simply need to know what your SQL Server's public IP is, and open a port in the firewall surrounding your server. Could be the default 1433 but it's good idea to obscure it and use a random port between 1024 to 65535.
Then with the SS authentication, which I assume either you will supply via your code or your user will fill it in just in time, you can construct a connection string. I usually would use DSN-less connection (you can google for Doug J. Steele's VBA sample on this) so I don't have to show the users the default DSN administrator dialogs. If you need the syntax for the connection strings, go to Carl Prothman's site for connection strings.
I hope that will help get you started.
The firewall and security is being taken care by the datacenter dba. I have not to worry about it. ADP application has already been tested with some SQL Serer name. Therefore, if SQL Serer name is replaced with SQL Server's public IP, the connection should be established.
I just checked the reference you have given. What is the implication of Network Library=DBMSSOCN. Is this library available in Win XP / Access 2003?
To connect to SQL Server running on a remote computer (via an IP address)
oConn.Open "Provider=sqloledb;" & _ "Network Library=DBMSSOCN;" & _ "Data Source=xxx.xxx.xxx.xxx,1433;" & _ "Initial Catalog=myDatabaseName;" & _ "User ID=myUsername;" & _ "Password=myPassword"Where:
- "Network Library=DBMSSOCN" tells OLE DB to use TCP/IP rather than
Named Pipes (Q238949)
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server. Q269882 and Q287932
- You can also add "Encrypt=yes" for encryption
To connect to SQL Server running on a remote computer (via an IP address)
oConn.Open "Provider=sqloledb;" & _ "Network Library=DBMSSOCN;" & _ "Data Source=xxx.xxx.xxx.xxx,1433;" & _ "Initial Catalog=myDatabaseName;" & _ "User ID=myUsername;" & _ "Password=myPassword"Where:
- "Network Library=DBMSSOCN" tells OLE DB to use TCP/IP rather than
Named Pipes (Q238949)
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server. Q269882 and Q287932
- You can also add "Encrypt=yes" for encryption
Yes, IP address is a simple way to guarantee that you can reach the SQL Server. Normally, if you were to use
SQL Server has several different protocols that can be used. I'm not an expert and could be very well mistaken, but in your situation, the only choice you have is TCP/IP protocol (well, actually, I'm not even sure it uses TCP even but that's entirely besides the point). The library 'DBMSSOCN' is what's responsible for providing the service of connecting a client to the server across the IP protocol.
Did that help?
SQL Server has several different protocols that can be used. I'm not an expert and could be very well mistaken, but in your situation, the only choice you have is TCP/IP protocol (well, actually, I'm not even sure it uses TCP even but that's entirely besides the point). The library 'DBMSSOCN' is what's responsible for providing the service of connecting a client to the server across the IP protocol.
Did that help?
Last edited:
Similar threads
- Replies
- 7
- Views
- 294
- Replies
- 10
- Views
- 670
- Replies
- 14
- Views
- 568
Users who are viewing this thread
Total: 1 (members: 0, guests: 1)