Still having trouble with remote connection

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Still having trouble with remote connection

Author

Topic

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-11-21 : 11:00:33

Hello all,

I'm still having trouble connecting to SQL Server Express from a remote machine.

I read this article

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

and followed all of its steps. Remote access is enabled through TCP/IP and VIA. The ports are listening when I check netstat -a and netstat -abn on the server computer.

However, my client program still can't connect to the server.

Here is my connection string:

"Data Source=MACHINENAME\SQLEXPRESS;AttachDbFilename=C:\Database\Database.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;Asynchronous Processing=true"

With this connection string, I can connect to the DB only on the server machine.

I've read something about the User Instance part of the string, but nothing really conclusive. All I know is when I have set it to false, I can't connect to the DB on the server machine either.

Not sure where to go from here...

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-21 : 11:24:12

Express edition does not support the Virtual Interface Adapter (VIA) so that is probably your issue.

Future guru in the making.

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-11-21 : 14:00:35

I changed the VIA option back to disabled, reset the computer and everything, but still no connection from the remote computer.

Is there an alternate way to write the connection string?

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-21 : 14:26:28

I believe Integrated Security should be SSPI instead of TRUE, or Trusted_Connection=yes

See:
http://www.connectionstrings.com/?carrier=sqlserver2005

Future guru in the making.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-21 : 16:21:40

Can you connect to the db with sql tools?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-22 : 05:28:06

Integrated security will be your problem as Zoroaster said.

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-11-22 : 09:46:57

rmiao, I don't have sql tools installed.

I'm going to change the Integrated Security option to SSPI and I'll get back to you guys.

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-11-22 : 10:25:11

OK. I made some changes, based on the link Zoroaster provided.

My connection string now looks like this:

Data Source=MACHINE\SQLEXPRESS;Initial Catalog=Database.mdf;Integrated Security=SSPI;Asynchronous Processing=true;

When I use this, I still get the error that SQL Express doesn't allow remote connections.

SO then I tried this:

Data Source=MACHINE\SQLEXPRESS,2301;Initial Catalog=Database.mdf;Integrated Security=SSPI;Asynchronous Processing=true;

When I did this, I got an error stated that MACHINE\\Guest failed to log on. So I went back to the server machine and saw that the guest account was turned off. I turned it on and I plan to go back and see what happens.

The reason I did 2301 was because in one article, it said to set the TCP/IP Port for IPAll to 2301.

Jeez, I hope I'm close. This is racking my nerves!

Am I getting hotter or colder?

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-11-22 : 10:33:24

Here is some more detailed info on my SQL Server Configuration Manager (Local):

SQL Server 2005 Network Configuration->Protocols for SQLEXPRESS:
->Shared Memory=Enabled
->Named Pipes=Enabled
->Pipe Name=\\.\pipe\MSSQL$SQLEXPRESS\sql\query
->TCP/IP Properties
->Protocol
Enabled=Yes
Keep Alive=30000
Listen All=Yes
No Delay=No
->IP Addresses
Active=Yes
Enabled=Yes
IP Address=xxx.xxx.x.xx
TCP Dynamic Ports=0
TCP Port=
->IPAll
TCP Dynamic Ports=1032
TCP Port=2301
->VIA=Disabled

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-22 : 16:35:47

Did you enable remote access on sql server? Check it in sql server configuration manaber, and ensure sql server browser service is running. By the way, you should use either dynamic or static port. Can't have both. Double check port number used in sql server log. If you don't have sql tools, can test connection in odbc.

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-11-23 : 09:50:59

rmiao,sqlbrowser.exe is running. Also, it is an exception in the windows firewall, along with sqlserver.exe

I also removed the TCP Port=2301 yesterday.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources