StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » Exposing SQL Server 2005 to the web

Exposing SQL Server 2005 to the web

Keith Chisarik — Tue, 22 Aug 2006 15:28:41 GMT

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

RE: Exposing SQL Server 2005 to the web

Trent L. Taylor — Tue, 22 Aug 2006 15:28:41 GMT

All very good news! :)

RE: Exposing SQL Server 2005 to the web

Keith Chisarik — Tue, 22 Aug 2006 15:26:15 GMT

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.

:D:D:D:D:D:D:D:D:D:D:D

RE: Exposing SQL Server 2005 to the web

Trent L. Taylor — Tue, 22 Aug 2006 15:18:10 GMT

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. :ermm:

RE: Exposing SQL Server 2005 to the web

Trent L. Taylor — Tue, 22 Aug 2006 15:14:53 GMT

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 :)

RE: Exposing SQL Server 2005 to the web

Keith Chisarik — Tue, 22 Aug 2006 15:12:33 GMT

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.

RE: Exposing SQL Server 2005 to the web

Keith Chisarik — Tue, 22 Aug 2006 14:53:43 GMT

I'm on it.........

RE: Exposing SQL Server 2005 to the web

Trent L. Taylor — Tue, 22 Aug 2006 14:52:27 GMT

That's more than likely the issue. If you can get a telnet to work, then you should be good.

RE: Exposing SQL Server 2005 to the web

Keith Chisarik — Tue, 22 Aug 2006 14:48:47 GMT

I cannot. I guess that means I need to recheck my firewall settings?

RE: Exposing SQL Server 2005 to the web

Trent L. Taylor — Tue, 22 Aug 2006 14:42:50 GMT

Can you telnet to the server?

telnet PUBLICIPADDRESS 1433

RE: Exposing SQL Server 2005 to the web

Keith Chisarik — Tue, 22 Aug 2006 14:40:08 GMT

Still no dice :crying:

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

RE: Exposing SQL Server 2005 to the web

Trent L. Taylor — Tue, 22 Aug 2006 14:29:49 GMT

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

RE: Exposing SQL Server 2005 to the web

Keith Chisarik — Tue, 22 Aug 2006 14:26:04 GMT

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.

RE: Exposing SQL Server 2005 to the web

StrataFrame Team — Tue, 15 Aug 2006 13:30:30 GMT

[quote][b]Keith Chisarik (08/15/2006)[/b][hr]PS - Im officially on board as of an hour ago, your stuck with me.

:cool:[/quote]

I wouldn't call it stuck :)

RE: Exposing SQL Server 2005 to the web

StrataFrame Team — Tue, 15 Aug 2006 13:29:59 GMT

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.

RE: Exposing SQL Server 2005 to the web

Keith Chisarik — Tue, 15 Aug 2006 13:29:11 GMT

PS - Im officially on board as of an hour ago, your stuck with me.

:cool:

RE: Exposing SQL Server 2005 to the web

Keith Chisarik — Tue, 15 Aug 2006 13:27:25 GMT

Bah cant edit......

Sorry it is developer edition for now in development. Eventually either Workgroup or Standard, but I'll cross that bridge later :D

RE: Exposing SQL Server 2005 to the web

Keith Chisarik — Tue, 15 Aug 2006 13:26:09 GMT

Fresh install of SQL 2005.

RE: Exposing SQL Server 2005 to the web

StrataFrame Team — Tue, 15 Aug 2006 13:12:47 GMT

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.