Remote access to SQL database
-
Hi,
This is surely more simple than it seems. I'll give you the background of the set up.
I have a Windows 2008 R2 server hosted in a data centre. It is not on a domain. It has SQL 2008 R2 on it. I wish to allow someone to connect to the databases on that server without them having access to the whole server via RDP.
I have created a Windows account for the user. It works, and I can log in using that account. I also enabled a Windows VPN connection to the server. I have no idea if I did this correctly, so don't assume I did, however, when I connect to the VPN from my local computer which is at home, I can connect to the VPN, but I can't do anything else.
I have SQL Management Studio installed on my local computer. What I would like is to allow someone to connect to the VPN so that they have access to connect to SQL on the server. SQL on the server is set up to allow mixed mode authentication because of the applications that run on it.
I could simply open port 1433 on the firewall but that allows anyone in the world to attempt a connection which I do not want. Can someone help me with some step by step instructions on getting this working?
At the moment, if I connect the VPN, which seems to work, I load SQL here and in the server name I can try either the server name or the IP and neither connect. It fails with the error shown in the attachment. I can verify that the server has remote connections enabled.
Any help appreciated.
~L
Regards,
Lee -
Did you enabled the TCP/IP protocol in the SQL Server Network configuration?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
Lee Savidge (1/30/2015)
Hi,I have SQL Management Studio installed on my local computer. What I would like is to allow someone to connect to the VPN so that they have access to connect to SQL on the server. SQL on the server is set up to allow mixed mode authentication because of the applications that run on it.
I could simply open port 1433 on the firewall but that allows anyone in the world to attempt a connection which I do not want. Can someone help me with some step by step instructions on getting this working?
~L
Hi Lee,
If you are allowing remote access only to SQL Server Database on a Windows server with a 'sql login' have you tried these steps.
1) Identify IP address of your server.
2) Identify the Port used for SQL Server
(I dont understand why you stated that it's wide open if using default port - a user still needs to be in SQL Server to get access - could you explain)
3) Use connects via VPN
4) Once authenticated uses local SSMS
connects via IP: (e.g. if ip is 192.1.1.1)
connection could be in the server name: 192.1.1.1,1433
login would be a 'sql login' you have created since you mentioned you don't have windows logins setup.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully
-
Yes, TCP is enabled
Regards,
Lee -
Hi Lee,
If you are allowing remote access only to SQL Server Database on a Windows server with a 'sql login' have you tried these steps.
1) Identify IP address of your server.
2) Identify the Port used for SQL Server
(I dont understand why you stated that it's wide open if using default port - a user still needs to be in SQL Server to get access - could you explain)
3) Use connects via VPN
4) Once authenticated uses local SSMS
connects via IP: (e.g. if ip is 192.1.1.1)
connection could be in the server name: 192.1.1.1,1433
login would be a 'sql login' you have created since you mentioned you don't have windows logins setup.
1. The server has an external IP which I know because I use that to connect to it via RDP
2. 1433
b. If I open port 1433 on the firewall, anyone in the world can try and connect to that port and potentially connect if they crack a password
3. Yes, I have set up a VPN to the server, and it allows me to connect, but I still can't access the SQL Server using SSMS on my local machine unless Ipoen the port on the firewall, but that negates the need for the VPN.
To verify, I have opened port 1433 on the firewall. Now I can connect to the SQL server remotely with or without the VPN which isn't what I want. I only want someone to be able to conenct to SQL once they have the VPN conencted. How do I do that?
Thanks,
~L
Regards,
Lee -
That sounds an awful lot like you have this server sat "on the internet", so please make sure you don't open the firewall!
Also, it sounds like you are trying to use the public IP address even when connected via the VPN, which would be wrong.
You need to determine the internal IP address of the server - one that is accessible via the VPN, not the public IP.
This internal IP address should be the one you use when you are trying to connect SSMS via the VPN.
You would need to examine the VPN setup to determine this internal address - as the VPN server will be assigning an IP address to your connected client, usually from it's own pool of addresses, and it will need to route the traffic from the remote client over the VPN to the internal address of the server.
This is all very general advice, as I don't know your setup.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0); - Forum Etiquette: How to post Reporting Services problems [/url]
- Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden [/url]
- How to Post Performance Problems - by Gail Shaw [/url]
mister.magoo (1/31/2015)
That sounds an awful lot like you have this server sat "on the internet", so please make sure you don't open the firewall!Also, it sounds like you are trying to use the public IP address even when connected via the VPN, which would be wrong.
You need to determine the internal IP address of the server - one that is accessible via the VPN, not the public IP.
This internal IP address should be the one you use when you are trying to connect SSMS via the VPN.
Ditto to what Mister Magoo said. Internally you might open up network communications 1432, externally inside a firewall the 'public' would normally not be able to access servers inside. Also you can also change the port as someone else mentioned by going to sql configuration and tcp/ip settings to change that. You might need to open up the newly assigned port though on your windows server if they are locked down.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully
Windows authentication will not work. The server is not on a domain, it cannot authenticate the user to anything.
Try this.
1. Try creating an ODBC connection from your box.
2. Add a SQL login. Make it sa.
Try connecting to the server using this login.
In the servername in SSMS, add the port after the IP separated by a comma.
111.11.11.111,14XX
If it connects, then reduce the permissions of the login and see if it works.
You do not need to open the firewall, but you will need to create a rule from the VPN to the server.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Hi,
Yes, the server is directly on the internet. I checked ipconfig and ther eisn't an internal IP range defined. I'll check to see if the data centre can apply one.
~L
Regards,
Lee
Well, trying to set this all up didn't quite work to plan. I ended up getting locked out of the server completely which was a bit of a bind.
So, could it be that I can set up the firewall, and restrict access by client IP?
Regards,
Lee
Is the VPN directly into the server?
If so, connect the VPN, then on the client, open a dos prompt and type
ipconfig /all | find "DHCP Server"
Hopefully, this will reveal the IP address the server is using for the VPN network.
Now, remote desktop onto the server and check whether SQL Server is listening on that network (either through the configuration manager or by just trying to connect to the address you got from "DHCP Server" using SSMS).
If it is listening ok, try to connect to that "DHCP Server" address using SSMS from the client.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);