By Keith Chisarik - 8/15/2006
Hello,
This one is mostly for Ben I guess since we discussed this in some length at the seminar.
I am looking for some help in exposing my SQL Server 2005 to the internet for development of the "smart client" app we talked about. I looked around for some articles but didnt find exactly what I was looking for. Can you point me in the right direction? I did talk to my dedicated server provider and they said "No problem, we will take care of it" once I go into production they will handle the security aspects. What I need right now is to get my in-house SQL 2005 server exposed for development and testing purposes in a relatively secure manner.
Thanks,
Keith
|
By StrataFrame Team - 8/15/2006
What version of SQL Server is it? The TCP/IP connectivity is going to vary depending upon the version.However, no matter what the version, you'll want to configure a user on the SQL Server that you will use to connect from the remote clients. Disable the 'sa' account and add some other account that is the sysadmin for the server. You'll also want to append "Encrypt=True;" to the end of your connection string to make sure the data is encrypted to and from SQL Server.
|
By Keith Chisarik - 8/15/2006
Fresh install of SQL 2005.
|
By Keith Chisarik - 8/15/2006
Bah cant edit......
Sorry it is developer edition for now in development. Eventually either Workgroup or Standard, but I'll cross that bridge later 
|
By Keith Chisarik - 8/15/2006
PS - Im officially on board as of an hour ago, your stuck with me.
|
By StrataFrame Team - 8/15/2006
Hehe, no problem... TCP communication will already be turned on for anything above the Express edition. So, the only thing you'll need to expose the server to the Internet is some way to expose the port. If the server has a public IP address, then you just need to open tcp ports 1433 and 1434. If the server has a private IP address, then you'll need to forward tcp ports 1433 and 1434 back to the private ip address of the server.
|
By StrataFrame Team - 8/15/2006
Keith Chisarik (08/15/2006)
PS - Im officially on board as of an hour ago, your stuck with me. I wouldn't call it stuck 
|
By Keith Chisarik - 8/22/2006
I have forwarded TCP ports 1433 and 1434 to my SQL server thru my PIX. No dice connecting from a remote location using 'PUBLICIPADDRESS\SERVERNAME' in the connection wizard. Any thoughts?
I do have SQL 2005 Enterprise Edition and an instance of SQL Express on the same box, I dont know if that could cause issues.
|
By Trent L. Taylor - 8/22/2006
You do not have to provide the server name, just the IP address unless you are using the SQLEXPRESS named instance.Enterprise Connection
PUBLICIPADDRESS Express Edition
PUBLICIPADDRESS\SQLEXPRESS
|
By Keith Chisarik - 8/22/2006
Still no dice 
I already made sure my server is listening on a static port (1433) instead of the default dynamic according to this article.
http://msdn2.microsoft.com/en-us/library/ms177440.aspx
|
By Trent L. Taylor - 8/22/2006
Can you telnet to the server?telnet PUBLICIPADDRESS 1433
|
By Keith Chisarik - 8/22/2006
I cannot. I guess that means I need to recheck my firewall settings?
|
By Trent L. Taylor - 8/22/2006
That's more than likely the issue. If you can get a telnet to work, then you should be good.
|
By Keith Chisarik - 8/22/2006
I'm on it.........
|
By Keith Chisarik - 8/22/2006
I have to use IP\SERVERNAME even in Management Studio internally.
I dont know if that is odd, I assume it is since you said I should have to.
|
By Trent L. Taylor - 8/22/2006
No, it isn't odd. You just have a named instance on the server which is fine. You can install SQL Server to be the default instance which doesn't require a name at the end, but this will work just fine
|
By Trent L. Taylor - 8/22/2006
I was just sitting here thinking though...if you are exposing this server publically you may have an issue with a named instance. Don't hold me to that, but it could cause you more heart-ache than being the default instance on the server which would be just an IP and a port externally versus including the name. This would be straight TCP/IP and should be a little more smooth to implement. Having the named instance could pose a problem. 
|
By Keith Chisarik - 8/22/2006
I got it.... there are multiple IP address settings on the 'IP ADDRESS' tab of the TCP/IP properties in Server Manager. I had to change all of them (except the loopback) including 'IPAll' to not use dynamic (clear the zero) and use 1433, not just the IP that NAT's from my public IP.
WOOT! I connected to my SQL Server with a strataframe app over the web, life is good. That was the last piece to make sure worked. Thanks! Hopefully someone else may find this useful someday, thanks for the help.
Now I just have to encrypt the stream like Ben said.
Also, I was able to connect to just the public IP address once I made this change.
|
By Trent L. Taylor - 8/22/2006
All very good news!
|