How do I have my app connect to a remote SQL Server that has port 1433 closed?

Hello,
I have a few applications that connect directly to a remote SQL Server,
mainly (but not only) so that web site owners can update the databases
that run their web sites.
The people that maintain the web/SQL server have now said (quite
sensibly) that they want to close port 1433 as it is a security risk. I
would like to know how my client applications are supposed to interact
with the SQL Server if they don't use port 1433. The ISP told me that
this server is the only one they have that has the port open, but they
wouldn't tell me how to do the data access - well, they would, but they
want consultancy rates for it!!
So, anyone any suggestions? The ISP's only suggestion was to have a
private ASP on one of the web sites, that you send the connection
details and SQL, and it returns a recordset as XML. The client
applications could open the recordset from the URL. They did say that
they would put Windows security on the page, so anyone trying to get at
it would need a UID and password, and that the actual URL should be
encoded in the client application so no-one would ever know it exists,
but it still sounds like a major security hole to me. I'm sure there are
better ways of doing this.
I'm sure plenty of people have found a secure way around this, but my
searches haven't turned up anything helpful.
Anyone any suggestions? TIA.
Alan Silver
(anything added below this line is nothing to do with me)I may be missing some details somewhere but here are a few
thoughts/questions:
1. If you are running a web based application, why not make it a real thin
web client and have the relevant connection info/code reside on the web
server that's protected behind your ISP's firewall? That way, clients point
to the web server and interact only with the web server. SQL Server access
is controlled by the web server and is invisible to your clients.
2. Having SQL Server's connection info hidden/encoded in the client
application hoping that nobody will ever know it exists is pretty naive. It
may deter the average user but even script kiddies can figure out ways to
capture the URL information. Just looking at the outgoing stream at the
proxy server or turning on the sniffer will probably do the trick.
Btw, you can configure the default port to talk to from the Client Network
Utility. Just highlight TCP/IP and click on the Properties button.
joe.
"Alan Silver" <alan-silver@.nospam.thanx> wrote in message
news:gRxjBXJ5YuPCFwup@.nospamthankyou.spam...
> Hello,
> I have a few applications that connect directly to a remote SQL Server,
> mainly (but not only) so that web site owners can update the databases
> that run their web sites.
> The people that maintain the web/SQL server have now said (quite sensibly)
> that they want to close port 1433 as it is a security risk. I would like
> to know how my client applications are supposed to interact with the SQL
> Server if they don't use port 1433. The ISP told me that this server is
> the only one they have that has the port open, but they wouldn't tell me
> how to do the data access - well, they would, but they want consultancy
> rates for it!!
> So, anyone any suggestions? The ISP's only suggestion was to have a
> private ASP on one of the web sites, that you send the connection details
> and SQL, and it returns a recordset as XML. The client applications could
> open the recordset from the URL. They did say that they would put Windows
> security on the page, so anyone trying to get at it would need a UID and
> password, and that the actual URL should be encoded in the client
> application so no-one would ever know it exists, but it still sounds like
> a major security hole to me. I'm sure there are better ways of doing this.
> I'm sure plenty of people have found a secure way around this, but my
> searches haven't turned up anything helpful.
> Anyone any suggestions? TIA.
> --
> Alan Silver
> (anything added below this line is nothing to do with me)|||>I may be missing some details somewhere but here are a few
>thoughts/questions:
Thanks, I have commented below...

>1. If you are running a web based application, why not make it a real thin
>web client and have the relevant connection info/code reside on the web
>server that's protected behind your ISP's firewall? That way, clients point
>to the web server and interact only with the web server. SQL Server access
>is controlled by the web server and is invisible to your clients.
I have web sites that run from databases. In order to allow the web site
owners access to modify the databases, I have admin applications that
install on their PCs. At the moment, those applications use a straight
connection to port 1433 on the server.
If I understand you correctly, you are suggesting moving to a web-based
admin. Apart from the amount of work required to rewrite the
applications, this also gives the added problem of extra pages on the
site that I don't want anyone (other than the site owners) to see.

>2. Having SQL Server's connection info hidden/encoded in the client
>application hoping that nobody will ever know it exists is pretty naive. It
>may deter the average user but even script kiddies can figure out ways to
>capture the URL information. Just looking at the outgoing stream at the
>proxy server or turning on the sniffer will probably do the trick.
Yup, that was my thought, which is why I posted the question here
looking for a better answer.
It's worth pointing out that the client app is not freely available, it
is only supplied to customers, so it's not as much of a problem as it
would be in other cases. Not that this removes the worry about people
hacking the app, but it is a slight mitigating factor.
Also, having the info encoded in the client app is no worse than having
port 1433 open. They still have to find a uid and password. At least
with the URL approach I mentioned, they need to figure out the URL, then
figure out the parameters to send, then figure out the values to use,
then find the Windows uid and password for the page. That's a lot more
effort than simply bashing port 1433 with combinations of uid and
password.

>Btw, you can configure the default port to talk to from the Client Network
>Utility. Just highlight TCP/IP and click on the Properties button.
Surely that wouldn't really help. I imagine people looking for ways in
to a server will just probe all ports and see if they get a response.
Changing port wouldn't stop them for many picoseconds ;-)
Thanks for the reply, any further thoughts appreciated.

>"Alan Silver" <alan-silver@.nospam.thanx> wrote in message
>news:gRxjBXJ5YuPCFwup@.nospamthankyou.spam...
>
Alan Silver
(anything added below this line is nothing to do with me)|||Inline
joe.
"Alan Silver" <alan-silver@.nospam.thanx> wrote in message
news:GvcKeYAwtCQCFwwU@.nospamthankyou.spam...
> Thanks, I have commented below...
>
> I have web sites that run from databases. In order to allow the web site
> owners access to modify the databases, I have admin applications that
> install on their PCs. At the moment, those applications use a straight
> connection to port 1433 on the server.
> If I understand you correctly, you are suggesting moving to a web-based
> admin. Apart from the amount of work required to rewrite the applications,
> this also gives the added problem of extra pages on the site that I don't
> want anyone (other than the site owners) to see.
There are 2 different things here. If you have a custom application that is
being used to manage SQL Server, you can have a server component with the
ISP which this "Mangement App" talks to and this can listen on any port that
you want. The app then makes the connection to SQL Server from behind the
firewall. Not a pretty solution since it adds additional steps but it does
give you more control over how you want to layer things out plus it removes
the need to have 1433 exposed.
The same concept can be applied to a web based "management app" where all
the user gets is a very thin layer that hits the web server via some https
connection and issues whatever task instructions it wants (not SQL command).
The web server then makes the connection, again, from behind the firewall to
SQL Server on whichever port you choose. Do still be careful with SQL
Injections here even though the risk is quite a bit lower than sending SQL
across the wire. User responses are still a potential hole.
As far as pages is concerned, they can be "hidden" by filtering userIDs and
presenting only the objects relevant to the userID. You can also provide a
secondary login for administration. The ideas at the conceptual level really
don't change hugely from a rich client though the execution is very
different.

> Yup, that was my thought, which is why I posted the question here looking
> for a better answer.
> It's worth pointing out that the client app is not freely available, it is
> only supplied to customers, so it's not as much of a problem as it would
> be in other cases. Not that this removes the worry about people hacking
> the app, but it is a slight mitigating factor.
> Also, having the info encoded in the client app is no worse than having
> port 1433 open. They still have to find a uid and password. At least with
> the URL approach I mentioned, they need to figure out the URL, then figure
> out the parameters to send, then figure out the values to use, then find
> the Windows uid and password for the page. That's a lot more effort than
> simply bashing port 1433 with combinations of uid and password.
>
> Surely that wouldn't really help. I imagine people looking for ways in to
> a server will just probe all ports and see if they get a response.
> Changing port wouldn't stop them for many picoseconds ;-)
> Thanks for the reply, any further thoughts appreciated.
So you already know putting it in the app doesn't really help. :-)
I'm not in favor of having any SQL being sent across the wire. I'm pretty
wary of SQL Injection risks.

> --
> Alan Silver
> (anything added below this line is nothing to do with me)|||>Inline
Thanks Joe, some useful comments there. My ISP pointed out that they are
moving everything to .NET as the security is much better there. If I'm
going to do any major rewriting, it would probably be better to go that
way.
In the meantime, I might look at using the server component like you
mentioned. I guess this is a sort of DCOM approach, no? Given that my
data access is all modular, I would only have to change one common
module and then recompile all the client apps, so it might not be too
bad.
Thanks again for the comments.
Alan

>joe.
>"Alan Silver" <alan-silver@.nospam.thanx> wrote in message
>news:GvcKeYAwtCQCFwwU@.nospamthankyou.spam...
>There are 2 different things here. If you have a custom application that is
>being used to manage SQL Server, you can have a server component with the
>ISP which this "Mangement App" talks to and this can listen on any port tha
t
>you want. The app then makes the connection to SQL Server from behind the
>firewall. Not a pretty solution since it adds additional steps but it does
>give you more control over how you want to layer things out plus it removes
>the need to have 1433 exposed.
>The same concept can be applied to a web based "management app" where all
>the user gets is a very thin layer that hits the web server via some https
>connection and issues whatever task instructions it wants (not SQL command)
.
>The web server then makes the connection, again, from behind the firewall t
o
>SQL Server on whichever port you choose. Do still be careful with SQL
>Injections here even though the risk is quite a bit lower than sending SQL
>across the wire. User responses are still a potential hole.
>As far as pages is concerned, they can be "hidden" by filtering userIDs and
>presenting only the objects relevant to the userID. You can also provide a
>secondary login for administration. The ideas at the conceptual level reall
y
>don't change hugely from a rich client though the execution is very
>different.
>
>
>So you already know putting it in the app doesn't really help. :-)
>I'm not in favor of having any SQL being sent across the wire. I'm pretty
>wary of SQL Injection risks.
>
>
Alan Silver
(anything added below this line is nothing to do with me)

How do I find out whats blocking port 1433?

I am running SQL Server 2000 sp4 in local mode on my laptop. I have some
other software which uses 1433 to access SQL. Remember this is all running
locally on a laptop. Port 1433 is being blocked. I have both the Windows
Firewall and the Mcaffee Firewalls disabled.
How can I find out what is blocking the port?
How do you know it's an issue with a blocked port?
If you are receiving an error, post the exact error number
and all details of the message.
-Sue
On Tue, 27 Mar 2007 19:04:07 -0700, KenRoy
<KenRoy@.discussions.microsoft.com> wrote:

>I am running SQL Server 2000 sp4 in local mode on my laptop. I have some
>other software which uses 1433 to access SQL. Remember this is all running
>locally on a laptop. Port 1433 is being blocked. I have both the Windows
>Firewall and the Mcaffee Firewalls disabled.
>How can I find out what is blocking the port?
|||Are you sure you have TCP/IP enabled (and configured for port 1433)? Try
enabling Named Pipes and specify that in an alias and try to connect with QA
that way.
You can use the DOS tracert command to check paths from one ip point to
another.
TheSQLGuru
President
Indicium Resources, Inc.
"KenRoy" <KenRoy@.discussions.microsoft.com> wrote in message
news:69A0FF05-54DD-4135-B9BC-6EA2BECEDEF3@.microsoft.com...
>I am running SQL Server 2000 sp4 in local mode on my laptop. I have some
> other software which uses 1433 to access SQL. Remember this is all
> running
> locally on a laptop. Port 1433 is being blocked. I have both the Windows
> Firewall and the Mcaffee Firewalls disabled.
> How can I find out what is blocking the port?
|||Yes, I am sure it is enabled for port 1433.
"TheSQLGuru" wrote:

> Are you sure you have TCP/IP enabled (and configured for port 1433)? Try
> enabling Named Pipes and specify that in an alias and try to connect with QA
> that way.
> You can use the DOS tracert command to check paths from one ip point to
> another.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "KenRoy" <KenRoy@.discussions.microsoft.com> wrote in message
> news:69A0FF05-54DD-4135-B9BC-6EA2BECEDEF3@.microsoft.com...
>
>
|||Tried to connect with Telnet (telnet localhost 1433) and it say's it blocked.
"Sue Hoegemeier" wrote:

> How do you know it's an issue with a blocked port?
> If you are receiving an error, post the exact error number
> and all details of the message.
> -Sue
> On Tue, 27 Mar 2007 19:04:07 -0700, KenRoy
> <KenRoy@.discussions.microsoft.com> wrote:
>
>
|||I have been trying to solve this problem myself over the last three days
having moved to a new dedicated server with a new host.
Old server was Win2k3 SP1
New server is Win2k3 SP2
I found that the IP Security policy was, by default, set to drop all
packets to 1433\1434.
Add the IP Security Policy snap in to MMC and you'll find it.
This change is in addition to configuring windows firewall.
Jarrod.
*** Sent via Developersdex http://www.codecomments.com ***

How do I find out whats blocking port 1433?

I am running SQL Server 2000 sp4 in local mode on my laptop. I have some
other software which uses 1433 to access SQL. Remember this is all running
locally on a laptop. Port 1433 is being blocked. I have both the Windows
Firewall and the Mcaffee Firewalls disabled.
How can I find out what is blocking the port'How do you know it's an issue with a blocked port?
If you are receiving an error, post the exact error number
and all details of the message.
-Sue
On Tue, 27 Mar 2007 19:04:07 -0700, KenRoy
<KenRoy@.discussions.microsoft.com> wrote:

>I am running SQL Server 2000 sp4 in local mode on my laptop. I have some
>other software which uses 1433 to access SQL. Remember this is all running
>locally on a laptop. Port 1433 is being blocked. I have both the Windows
>Firewall and the Mcaffee Firewalls disabled.
>How can I find out what is blocking the port'|||Are you sure you have TCP/IP enabled (and configured for port 1433)? Try
enabling Named Pipes and specify that in an alias and try to connect with QA
that way.
You can use the DOS tracert command to check paths from one ip point to
another.
TheSQLGuru
President
Indicium Resources, Inc.
"KenRoy" <KenRoy@.discussions.microsoft.com> wrote in message
news:69A0FF05-54DD-4135-B9BC-6EA2BECEDEF3@.microsoft.com...
>I am running SQL Server 2000 sp4 in local mode on my laptop. I have some
> other software which uses 1433 to access SQL. Remember this is all
> running
> locally on a laptop. Port 1433 is being blocked. I have both the Windows
> Firewall and the Mcaffee Firewalls disabled.
> How can I find out what is blocking the port'|||Yes, I am sure it is enabled for port 1433.
"TheSQLGuru" wrote:

> Are you sure you have TCP/IP enabled (and configured for port 1433)? Try
> enabling Named Pipes and specify that in an alias and try to connect with
QA
> that way.
> You can use the DOS tracert command to check paths from one ip point to
> another.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "KenRoy" <KenRoy@.discussions.microsoft.com> wrote in message
> news:69A0FF05-54DD-4135-B9BC-6EA2BECEDEF3@.microsoft.com...
>
>|||Tried to connect with Telnet (telnet localhost 1433) and it say's it blocked
.
"Sue Hoegemeier" wrote:

> How do you know it's an issue with a blocked port?
> If you are receiving an error, post the exact error number
> and all details of the message.
> -Sue
> On Tue, 27 Mar 2007 19:04:07 -0700, KenRoy
> <KenRoy@.discussions.microsoft.com> wrote:
>
>|||I have been trying to solve this problem myself over the last three days
having moved to a new dedicated server with a new host.
Old server was Win2k3 SP1
New server is Win2k3 SP2
I found that the IP Security policy was, by default, set to drop all
packets to 1433\1434.
Add the IP Security Policy snap in to MMC and you'll find it.
This change is in addition to configuring windows firewall.
Jarrod.
*** Sent via Developersdex http://www.codecomments.com ***

How do I determine the IP number and TCP Port of a SQL instance

I'm trying to determine the specific IP address and the TCP port on which a SQL server is communicating.

As far as I can tell, I can't use SMO for this, since I need a method that is compatible with both SQL 2005 AND SQL 2000. Is there a work around or can I use DMO?

Since my target is to be able to use this code against multipel servers, it would be nice to find the method with the least overhead possible. I would also like to avoid being forced to read registries remotely.

--
Jakob BindsletLook at the ServerIPAddress and IPAddressProperty values within the Microsoft.SqlServer.Management.Smo.Wmi namespace.|||
http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/19/Default.aspx

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||According to BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9/html/6527388b-26b5-4ebb-8a52-0cf4415f9134.htm) the Microsoft.SqlServer.management.Smi.Wmi namespace isn't supported on SQL Server 2000 and below.|||

I don't see why the SQL Server version has any bearing on what information WMI will provide. I'm trying to work out the details but have run into a bit of confusion regarding the IP Address, but I'm getting the Port information back. I've blogged about it at http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/11/30/25785.aspx if you're interested.

|||

Hi Jagoop,

Did you find a way to retrieve this information for both SQL 2000 as well as SQL 2005 instances?

Thanks,

|||AS WMI is not supported you will have to use the registry directly:

using Microsoft.Win32;

namespace SMOProject

{

class Program

{

static void Main(string[] args)

{

string SQLServerKey = @."SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib";

RegistryKey rk = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, "JensOne");

RegistryKey protocols = rk.OpenSubKey(SQLServerKey);

Console.WriteLine(SQLServerKey);

foreach(string s in protocols.GetSubKeyNames())

{

RegistryKey rkprotocol = protocols.OpenSubKey(s);

Console.WriteLine("\tProtocol: {0}",rkprotocol.Name );

foreach (string ValueName in rkprotocol.GetValueNames())

{

Console.WriteLine("\t\t[Type]:Value: [{0}]:{1}",ValueName, rkprotocol.GetValue(ValueName));

}

}

HTH, Jens K. Suessmeyer.

http.//www.sqlserver2005.de

|||

I'd prefer to use DMO, instead of using the registry for this. It will also take care of the instance names for you.

See http://msdn2.microsoft.com/en-us/library/ms134942.aspx (Registry2).

Note you need to have the updated DMO version installed that ships with SQL Server 2005 and is also available as a redist as well (see the FAQ of this group) -- that is, if you plan to use it with SQL Server 2000 and 2005.

I need to lookup, but if I recall correctly it will work for SQL Server 2005 as well, as we aimed at making the port of SQL-DMO clients from 2000 to 2005 totally transparent.

|||

Thanks Jens,

If you have multiple ip addresses listening on different ports, the above registry location doesn't show that information. I tried searching for these values in the registry but couldn't find it anywhere. I have only seen this informaiton ouput to the SQL Server logs during the startup process. Is it possible to retrieve this information?

PK

|||

This is in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp

(For the first instance)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How do I determine the IP number and TCP Port of a SQL instance

I'm trying to determine the specific IP address and the TCP port on which a SQL server is communicating.

As far as I can tell, I can't use SMO for this, since I need a method that is compatible with both SQL 2005 AND SQL 2000. Is there a work around or can I use DMO?

Since my target is to be able to use this code against multipel servers, it would be nice to find the method with the least overhead possible. I would also like to avoid being forced to read registries remotely.

--
Jakob BindsletLook at the ServerIPAddress and IPAddressProperty values within the Microsoft.SqlServer.Management.Smo.Wmi namespace.|||
http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/19/Default.aspx

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||According to BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9/html/6527388b-26b5-4ebb-8a52-0cf4415f9134.htm) the Microsoft.SqlServer.management.Smi.Wmi namespace isn't supported on SQL Server 2000 and below.|||

I don't see why the SQL Server version has any bearing on what information WMI will provide. I'm trying to work out the details but have run into a bit of confusion regarding the IP Address, but I'm getting the Port information back. I've blogged about it at http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/11/30/25785.aspx if you're interested.

|||

Hi Jagoop,

Did you find a way to retrieve this information for both SQL 2000 as well as SQL 2005 instances?

Thanks,

|||AS WMI is not supported you will have to use the registry directly:

using Microsoft.Win32;

namespace SMOProject

{

class Program

{

static void Main(string[] args)

{

string SQLServerKey = @."SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib";

RegistryKey rk = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, "JensOne");

RegistryKey protocols = rk.OpenSubKey(SQLServerKey);

Console.WriteLine(SQLServerKey);

foreach(string s in protocols.GetSubKeyNames())

{

RegistryKey rkprotocol = protocols.OpenSubKey(s);

Console.WriteLine("\tProtocol: {0}",rkprotocol.Name );

foreach (string ValueName in rkprotocol.GetValueNames())

{

Console.WriteLine("\t\t[Type]:Value: [{0}]:{1}",ValueName, rkprotocol.GetValue(ValueName));

}

}

HTH, Jens K. Suessmeyer.

http.//www.sqlserver2005.de

|||

I'd prefer to use DMO, instead of using the registry for this. It will also take care of the instance names for you.

See http://msdn2.microsoft.com/en-us/library/ms134942.aspx (Registry2).

Note you need to have the updated DMO version installed that ships with SQL Server 2005 and is also available as a redist as well (see the FAQ of this group) -- that is, if you plan to use it with SQL Server 2000 and 2005.

I need to lookup, but if I recall correctly it will work for SQL Server 2005 as well, as we aimed at making the port of SQL-DMO clients from 2000 to 2005 totally transparent.

|||

Thanks Jens,

If you have multiple ip addresses listening on different ports, the above registry location doesn't show that information. I tried searching for these values in the registry but couldn't find it anywhere. I have only seen this informaiton ouput to the SQL Server logs during the startup process. Is it possible to retrieve this information?

PK

|||

This is in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp

(For the first instance)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de