Re: connecting to sql svr express over internet
- From: "Sylvain Lafontaine" <sylvainlafontaine2009@xxxxxxxx>
- Date: Fri, 28 Aug 2009 13:07:39 -0400
Beside removing the http:// as mentionned by Aaron, for the port number, it
must often be separated by a comma at the end of the server's address when
you when to specify it:
mydomainname.com\sqlexpress,1433
This is not always the case and it depends on the program used. You can
also add the prefix tcp: to specify the TCP protocol (or np: for named
pipe):
tcp:mydomainname.com\sqlexpress,1433
For ODBC, there are also situations where you must specify the correct
network library to be sure to use the TCP protocol. The best way of dealing
with these problem is to define in Alias - where you can specify the address
of the server and the protocol and the port (in the case of TCP) to be used.
See SQL Server Configuration Manager | SQL Native Client Configuration |
Aliases to create an alias.
As the port 1433 is not the regular port to be used with a named instance,
you should first verify on your LAN that you can indeed connect to the
server using this port.
Finally, you should check the configuration on the remote server, especially
the readdressing of the ports through the route: does the router know that
incoming connection on ports 1433 (and 1434?) must go to right machine (the
one hosting the SQL-Server)? For most router/modem where you can connect
more than a single machine, this configuration must usually be done even
when there is a single machine connected to the router.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
"kpg" <no@xxxxxxxx> wrote in message
news:Xns9C7573418A3CFipostthereforeiam@xxxxxxxxxxxxxxxx
Hi all,
I know this has been asked a gazillion times, and I've read how to do
this, and I've done it, it just will not work for me.
I have Sql Server 2005 Express edition installed on a server. I'm not
100% clear on how different the express is from the regular, I think
it's just a db size restriction, and maybe max number of connections,
and not for use on a cluster, but I believe it should have no effect on
internet access.
So I used the sql svr surface area config tool to allow remote tcp
connections, and I can access the db over my LAN using the server's
netbios name.
I am using sql svr authentication to log in to the db, not windows.
I did not think the server was a named instance, however, I read that to
tell you can look at the service name for the SQL service, [it's Sql
Service (SQLEXPRESS)] and also looking at the properties of the service,
if there is a -s[name] then it is a named instance, and mine is -
sSQLEXPRESS, so I'm now thinking I do have a named instance.
I used the sql svr configuration mgr to allow tcp and set port 1433 and
turned off dynamic ports for all ips, localhost and the server's lan ip
(overkill, but just to be sure) and I made sure to stop and re-start the
sql service.
I configured my router's firewall to allow ports 1433-1434 both tcp and
udp and directed this port to my sql server's lan ip.
So if I use a connection string of svr-002\sqlexpress I can connect over
my LAN, so tcp is working, remote connection is working, sql server is
working.
If I try a connect string of:
http:\\mydomainname.com
http:\\mydomainname.com\
http:\\mydomainname.com\sqlexpress
http:\\mydomainname.com\sqlexpress\
http:\\mydomainname.com:1433\sqlexpress
http:\\myIPAddr
http:\\myIPAddr:1433
http:\\myIPAddr\sqlexpress
http:\\myIPAddr:1433\SQLEXPRESS
...you get the idea, I've tried just about every way I can think of.
Backslashes are used in the lan connection that works, but I tried mixed
forward slashes as well in the http:// portion and the named instance
portion (I think MS pretty much treats forward and back slashes the
same)
I have repeated the above steps with different ports, and various other
changes to no avail.
In VS 2005 server explorer I get a "Server name <> could not be found."
From a vb6 app changing the connection in a linked access table I get:
Connection Failed:
SQLState '01000'
SQL Server Error: 14
[Microsoft][ODBC SQL server Driver][BDNETLIB]ConnectionOpen
(ParseConnectParams()).
Connection Failed:
SQLState '08001'
SQL Server Error: 14
[Microsoft][ODBC SQL server Driver][BDNETLIB]Invalid connection.
I know this is supposed to be easy so what could I possible be doing
wrong?
kpg
.
- Follow-Ups:
- References:
- Prev by Date: Re: Error View SQL Server 2008 Errorlog
- Next by Date: Re: connecting to sql svr express over internet
- Previous by thread: Re: connecting to sql svr express over internet
- Next by thread: Re: connecting to sql svr express over internet
- Index(es):
Relevant Pages
|
Loading
