Sunday, March 25, 2012
connecting to SQL DB through a firewall
I'm a bit new to all this, so please bear with me! :)
I've got a webserver in our DMZ and I'm trying to create an ODBC connection from that server to a db server within our firewall. When I try and connect, the following message appears:
Connection failed:
SQLState: '01000'
SQL Server Error: 10060
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()),
Connection failed:
SQL State: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied
I'm at a bit of a loss as to what's going on, as we have an application on the webserver that connects to another SQL DB server within our firewall with no problem!
webserver:
OS - win2000 standard server sp4
db server:
OS - WinNT 4.0
SQL - 7.0
If anyone can help, it would be much appreciated!!
Cheers,
Ewan :)I'd recommend (so does MS) to have a VPN tunnel for intrasver communications, especially if they are on opposite sides of the firewall.
Wednesday, March 7, 2012
Connecting to a remote server
firewall (172.16.0.1)
I would like this server to connect to a db that lives on an SQL Server
(PDC: 192.168.16.2) on our internal lan.
I have opened port 1433 on the firewall between 172.16.0.2 & 192.168.16.2
On the webserver I have installed MSDE that the web app currently connects
to successfully whilst I determine how to connect it to the internal server.
In EM on the SQL Server I can now connect to the MSDE server, have activated
TCP/IP 1433 on MSDE, and all works well.
I would like our Web application to used the internal SQL Server as opposed
to the local MSDE.
In order to connect to the SQL Server at 192.168.16.2 is MSDE required or
can it be removed ?
Do I simply need to specify the port in the querystring in order to connect
to the remote server ?
What users/logins will need to be created in order for the web app to
connect to our internal server ?
Should I use a trusted connection or specify uid=sa;pwd=abc in the
querystring ?
The querystring I have developed so far to connect to this server is:
<appSettings>
<add key="ConnectionString"
value="server=192.168.16. 2,1433;Trusted_Connection=true;database=
store" />
</appSettings>
Or should I use:
<appSettings>
<add key="ConnectionString"
value="server=192.168.16.2,1433;uid=sa;pwd=abc;database=store" />
</appSettings>
Any assistance would be greatly appreciated.
Thanks
MurphyAs a general comment you are better off not putting real IP Addresses in thi
s newsgroup (as it is open to hackers etc).
No, you don't need MSDE on the webserver (unless it is running Application C
enter 2000 which uses MSDE internally).
Two documents well worth the read are in the Architecture Blueprints (network_architect
ure.doc and security_architecture.doc) of the MS System Architecture 2.0 reference arch
itecture kit. http://www.microsoft.com/windowsser...sa/default.mspx
There is a good article on different security architecture models for .NET/S
QL applications here that's worth a read:
http://msdn.microsoft.com/library/d.../>
Netch12.asp|||Do I simply need to specify the port in the querystring in order to connect
to the remote server ?
What users/logins will need to be created in order for the web app to
connect to our internal server ?
Should I use a trusted connection or specify uid=sa;pwd=abc in the
querystring ?
The port is not required. SQL clients default to port 1433.
The logins required depends on your application.
MSDE is not required on the webserver.
The IIS server is the client to the SQL Server in your case.
You can't make Trusted Connections using a username and password.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks Kevin,
If I need other than the username and password to make a trusted connection
with the web server & sql server are in different domains what is be best
approach. I am now able to connect using sa & password in the querystring
but I'd prefer the trusted connection option...
Thanks
Murphy
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:FKpYx6cHEHA.1988@.cpmsftngxa06.phx.gbl...
> Do I simply need to specify the port in the querystring in order to
connect
> to the remote server ?
> What users/logins will need to be created in order for the web app to
> connect to our internal server ?
> Should I use a trusted connection or specify uid=sa;pwd=abc in the
> querystring ?
> The port is not required. SQL clients default to port 1433.
> The logins required depends on your application.
> MSDE is not required on the webserver.
> The IIS server is the client to the SQL Server in your case.
> You can't make Trusted Connections using a username and password.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||From previous post:
"If I need other than the username and password to make a trusted connection
with the web server & sql server are in different domains what is be best
approach. I am now able to connect using sa & password in the querystring
but I'd prefer the trusted connection option..."
Trusted connections rely on NT credentials. So if the machines are in
different domains, then there is no way
to correctly "impersonate" the client machine.
So, if you have two domains that are not Trusted, then the only way to make
this work is to duplicate the same NT username & password in each domain.
This technique is called "Workgroup Security".
It's more work on the administrator to keep the NT passwords in synch at
each domain, but it allows you to use NT credentials instead
of passing a SQL username and password.
If you put a Server certificate on the SQL Server , then the logins passed
will be encrypted. Additionally, you could enable SSL encryption between
the IIS machine and Server, which would encrypt both the logins and the
data.
See this article for steps to implement SSL
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Connecting to a remote server
Either of those connection strings should work. Use
trusted connection if your users will be authenticating
against the database, use username and password (but not
sa!!) if you can't use trusted.
Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com
>--Original Message--
>We are setting up a webserver (172.16.0.2) that lives in
the DMZ behind our
>firewall (172.16.0.1)
>I would like this server to connect to a db that lives
on an SQL Server
>(PDC: 192.168.16.2) on our internal lan.
>I have opened port 1433 on the firewall between
172.16.0.2 & 192.168.16.2
>On the webserver I have installed MSDE that the web app
currently connects
>to successfully whilst I determine how to connect it to
the internal server.
>In EM on the SQL Server I can now connect to the MSDE
server, have activated
>TCP/IP 1433 on MSDE, and all works well.
>I would like our Web application to used the internal
SQL Server as opposed
>to the local MSDE.
>In order to connect to the SQL Server at 192.168.16.2 is
MSDE required or
>can it be removed ?
>Do I simply need to specify the port in the querystring
in order to connect
>to the remote server ?
>What users/logins will need to be created in order for
the web app to
>connect to our internal server ?
>Should I use a trusted connection or specify
uid=sa;pwd=abc in the
>querystring ?
>The querystring I have developed so far to connect to
this server is:
> <appSettings>
> <add key="ConnectionString"
>value="server=192.168.16.2,1433;Trusted_Connection=true;d
atabase=store" />
> </appSettings>
>Or should I use:
> <appSettings>
> <add key="ConnectionString"
>value="server=192.168.16.2,1433;uid=sa;pwd=abc;database=s
tore" />
> </appSettings>
>Any assistance would be greatly appreciated.
>
>Thanks
>Murphy
>
>.
>Thanks for your assistance, I have got this to work now and successfully
removed MSDE from the WebServer and all is still working.
As my webserver and SQL server are in different subnets I was having
problems getting a trusted connection to work and could only open the
connection with sa, however I have read that this is not an advisable
practice...
My users will authenticate against the SQL server which will contain a table
of usernames and passwords.
When using the trusted connection does this mean that the account used by
IIS is the one that will connect to SQL server via the query string ? So if
IIS uses the IUSER_SERVER account then I guess this account must have access
to the SQL server db on the sql server machine.
Thanks
Murphy
"Invotion" <anonymous@.discussions.microsoft.com> wrote in message
news:1849201c41b13$9a998f20$a501280a@.phx
.gbl...
> You don't need to have MSDE on the webserver.
> Either of those connection strings should work. Use
> trusted connection if your users will be authenticating
> against the database, use username and password (but not
> sa!!) if you can't use trusted.
> Sincerely,
> Invotion Engineering Team
> Advanced Microsoft Hosting Solutions
> http://www.Invotion.com
>
> the DMZ behind our
> on an SQL Server
> 172.16.0.2 & 192.168.16.2
> currently connects
> the internal server.
> server, have activated
> SQL Server as opposed
> MSDE required or
> in order to connect
> the web app to
> uid=sa;pwd=abc in the
> this server is:
> atabase=store" />
> tore" />
Sunday, February 12, 2012
Connect to SQL through firewall?
I have a webserver sitting on the DMZ part of the firewall and my SQL Server sitting on the LAN side. I am trying to connect to the SQL Server using SqlConnection class. All ports are correctly opened and authentication is set to mix-mode. Can I do it without using Oledbconnection?
Connection Details
ConnectionString: "data source=mysqlserver;initial catalog=Northwind;persist security info=False;user id=webuser;pwd=password;workstation id=webbill;packet size=4096"
Client Network Utilitly: Server alias "aliassqlserver" created for mysqlserver using TCP/IP, servername poiting to the IP address of mysqlserver, dynamicall determine port. "aliassqlserver" comes up in the Enterprise manager, and can be connected using "sa". "webuser" permissions have been checked with access to Northwind.
Thanks in advanced guys...
PaulWhat error are you getting? What firewall do you have? Can you ping any computer on the DMZ side from the LAN side? The best thing is to start without any firewall rules.
- Boris
Friday, February 10, 2012
Connect to SQL Server 2000 over Internet
Specify the port along with the IP address e.g. 192.168.1.2,1433.
Where 1433 is the port. Also verify that SQL server allows mixed authentication and the specified port is opened if the conenction is made across the firewall.