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

You do not need to enter http:// ... You are not using HTTP protocol so I'm
not sure why you think you need that. A connection string would probably be
(I am supplying for technologies like ASP):

Provider = SQLOLEDB.1;
Data Source = <a.b.c.d>\SQLEXPRESS;
Initial Catalog = <database name>;
User ID = <username>;
Password = <password>;
Network = DBMSSOCN;

Where a.b.c.d is your IP address.




On 8/28/09 12:19 PM, in article
Xns9C7573418A3CFipostthereforeiam (AT) 207 (DOT) 46.248.16, "kpg" <no (AT) spam (DOT) com> wrote:

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 (AT) spam (DOT) com> wrote

Aaron Bertrand <moc.liamg (AT) dnartreb (DOT) noraa> wrote in
news:C6BD808D.232AA%moc.liamg (AT) dnartreb (DOT) noraa:

heh, that was it.

I saw an example somewhere with the http, but in my defense I did try it
wiithout it several times, but that may have been when I was using a
different port and I used a colon instead of a comma as suggested in
Sylvain's reply, or had something else out of place.

I was getting so fustrated at the whole thing I abonanded the "change only
one thing at a time' approach.


I was using port 1954 instead of the default 1433 (I know named instances
use dynamic ports, but I turned that off). Removing the http and not
specifying a port worked (so my client is using the defualt 1433). Next
step is to try and go back to a non-default port for security reasons.

Thanks a lot Aaron and Sylvain

kpg

"Sylvain Lafontaine" <sylvainlafontaine2009 (AT) yahoo (DOT) ca> wrote in
news:#qNZVIAKKHA.3708 (AT) TK2MSFTNGP02 (DOT) phx.gbl:

comma instead of colon? Interesting.

removing the http worked. See my reply to Aaron.

thanks