Connecting with SQL Server Express 2005 when Wireless is active

[Franco Lombardo]

Hi all.

I have a strange problem.

On a laptop I installed SQL Server express 2005.
If I'm not connected to a LAN, or if I'm connected via "copper", I can use
SQL Server through MS Access. If I open a connectio to a Wireless LAN, I
can't use my local DB. I use a trusted connection on TCP port 1433. Note
that anyway Managment Studio Express can manage the DB.

I tried to connect my SQL Server tables both as localhost\SQLEXPRESS and as
pc_name\SQLEXPRESS.

Any ideas?

Thanks a lot.

Bye

Franco

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.francolombardo.net
Scala, Java, As400.....
http://twitter.com/f_lombardo
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[Rick Byham, MSFT]

It sounds like you are connecting from MS Access to SQL Server on the same
computer. Normally that would use the shared memory protocol, not TCP.
I'm guessing that you are forcing TCP, either through your connection string
or your DSN. First suggestion, don't.

I think what's happening, is that your firewall profile is changing, from
Private to Public.
What is your Operating System? If it's Windows Vista or Windows 7, instead
of using the firewall.cpl program to manage your Windows firewall, use
wf.msc which is the Windows Firewall with Advanced Services. With that you
can see that there are three firewall profiles. Domain, Public, Private.
When you are not connected, you are using Private. When you connect to the
Wireless, you are probably switching to Public. Your TCP port access through
port 1433 is probably open on Private, and closed on Public. And since you
are forcing your connection to TCP, you are going through the firewall, even
though you're not going anywhere.
More info on the firewall settings at:
http://msdn.microsoft.com/en-us/library/cc646023.aspx
--
Rick Byham, MSFT
(Implies no warranty or rights)

"Franco Lombardo" <f_lom...@hotmail.com> wrote in message
news:%23dw1EZ4...@TK2MSFTNGP02.phx.gbl...

[Franco Lombardo]

> It sounds like you are connecting from MS Access to SQL Server on the same
> computer. Normally that would use the shared memory protocol, not TCP.
> I'm guessing that you are forcing TCP, either through your connection
> string or your DSN. First suggestion, don't.

As Lorenzo Benaglia suggested on the italian forum, I tried to use shared
memory, setting .\SQLEXPRESS as the connection string, but it didn't work.

> I think what's happening, is that your firewall profile is changing, from
> Private to Public.

It was my opinion too, but the problem still arises even if I disable the
firewall at all.

> What is your Operating System?

XP sp3.

Now I'm updating the PC with some pathces from MS. I'll let you know.

Anyway thank you very much for your suggestions!

Bye.

Franco

[Rick Byham, MSFT]

Sometime when it's working, create a pass-through query in Access, and sent
this query to SQL Server :

SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;

This will tell you which protocol (shared memory, named pipes, TCP/IP) is
working at that time.
That might give you a clue as to what is not working.

--
Rick Byham, MSFT
(Implies no warranty or rights)

"Franco Lombardo" <f_lom...@hotmail.com> wrote in message

news:%23GH59$75KH...@TK2MSFTNGP04.phx.gbl...

[Franco Lombardo]

> Sometime when it's working, create a pass-through query in Access, and
> sent this query to SQL Server :
>
> SELECT net_transport FROM sys.dm_exec_connections WHERE session_id =
> @@SPID;

Ouch, this query tells me that I use TCP even if I have set .\SQLEXPRESS as
the DB name. The connection string I use is:

ODBC;DRIVER=SQL Server;SERVER=.\SQLEXPRESS;UID=myName;APP=2007 Microsoft
Office system;DATABASE=myDBName;Trusted_Connection=Yes;TABLE=dbo.MyTable

How can I tell SQL Server to use shared memory?

Thanks a lot.

Bye

Franco

--

[Franco Lombardo]

> How can I tell SQL Server to use shared memory?

I solved disabling TCP and setting Shared memory as the only active
protocol.
Then I used (local)\SQLEXPRESS in the connection string.

Thanks a lot.

Bye

Franco

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[Rick Byham, MSFT]

You can for the protocol by including lpc: np: or tcp: just before the
server name. lpc: is used for shared memory because it is a local pipe. np:
for named pipes. tcp: for TCP.

So changing SERVER=.\SQLEXPRESS to SERVER=lpc:.\SQLEXPRESS would probably
force shared memory as well.
But that assumes your ODBC provider can handle that. But since you are
probably going through the SQL Server Native Client, it should work.

--
Rick Byham, MSFT
(Implies no warranty or rights)

"Franco Lombardo" <f_lom...@hotmail.com> wrote in message

news:eh3ueAD6...@TK2MSFTNGP05.phx.gbl...

[Erland Sommarskog]

Franco Lombardo (f_lom...@hotmail.com) writes:
> I solved disabling TCP and setting Shared memory as the only active
> protocol.
> Then I used (local)\SQLEXPRESS in the connection string.

Just remember this, in case you want to connect remotely to the
databas in the future.

Rick mentioned SQL Native client, I think you should change the connection
string to say:

Driver={SQL Native Client}

"SQL Server" gives you the old ODBC driver for SQL 2000. I seem to recall
that I have had some problems using this driver to connect to shared
memory to named instances. Not always, but in some situations that I
never fully understood.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Franco Lombardo]

Thank you all for your great answers.