Connect to SQL 2005 database on local network fails

[Gary W. Smith]

I'm running into an odd problem. I'm working on a PPC2003se
application that will pull information form a central database and dump
in into a local database. The backend database is SQL 2005 Express and
the local is SQL 2005 CE.

I have enabled virtual NE2000 network card in the enulated PPC and when
I start it I can open connections to various external web sites. All
looks well. When I start VS 2005 and run the app I loose network
connectivity. This was the first odd problem. So I enabled DMA
network transfers and cradled the PPC and I can open up connections to
various web sites after the app is deployed from VS 2005. So I know I
have some connectitivy now.

When I run the code sniplet below I always get failed access to the
network. I have also tested this with the firewall on my workstation
off. Basically I continually get "SQL Server does not exist or access
denied." The login does indeed work as I can hit it with SQL Manager
using these credentials, IP and port.

Basically I have two questions; 1) is the code sniplet below rational
for connecting to a SQL server from a Pocket PC 2) what is the best way
to emulate network connectivity in a development environment, given
that the end users will either be connected via wireless (LAN) or via
cradle.



Calling function...

Dim oRepl As New Replicatio
oRepl.LocalDBName = frmMain.AppPath & "\mobile.sdf"
oRepl.LocalDBEncrypt = False
oRepl.LocalDBPass = ""

oRepl.RemoteDBUser = "MobileUser"
oRepl.RemoteDBPass = "password"
oRepl.RemoteDBName = "dev_mobile"
oRepl.RemoteDBServer = "10.0.54.2\SQLEXPRESS,27858" 'Verified
that TCP is enabled.

oRepl.Push()
oRepl.Pull()


Public Class Replication

Private _sLocalDBName As String
Private _sLocalDBPass As String
Private _bLocalDBEncrypt As Boolean
Private _sRemoteDBServer As String
Private _sRemoteDBName As String
Private _sRemoteDBUser As String
Private _sRemoteDBPass As String

Public Function Pull() As Integer
Dim oLocalConn As New SqlServerCe.SqlCeConnection
Dim oLocalCommand As SqlServerCe.SqlCeCommand
Dim oLocalRS As SqlServerCe.SqlCeDataReader

Dim oRemoteConn As New SqlClient.SqlConnection
Dim oRemoteCommand As SqlClient.SqlCommand
Dim oRemoteRS As SqlClient.SqlDataReader


oLocalConn.ConnectionString = "data source=" & _sLocalDBName &
";password=" & _sLocalDBPass & ";encrypt database=" &
_bLocalDBEncrypt.ToString
oLocalConn.Open()
' Clear Clients
oLocalCommand = New SqlServerCe.SqlCeCommand("DELETE FROM
clients", oLocalConn)
oLocalCommand.ExecuteNonQuery()


oRemoteConn.ConnectionString = "server=" & _sRemoteDBServer &
";uid=" & _sRemoteDBUser & ";pwd=" & _sRemoteDBPass & ";database=" &
_sRemoteDBName

oRemoteConn.Open()
oRemoteCommand = New SqlClient.SqlCommand("SELECT client_id,
client_name FROM clients ORDER BY client_id", oRemoteConn)
oRemoteRS = oRemoteCommand.ExecuteReader
If oRemoteRS.Read Then
oLocalCommand = New SqlServerCe.SqlCeCommand("INSERT INTO
clients(client_id, client_name, is_new) VALUES(@client_id,
@client_name, 0)", oLocalConn)
Dim iClientID As Integer
Dim sClientName As String
Do
If Not IsDBNull(oRemoteRS("client_id")) Then iClientID
= oRemoteRS("client_id") Else iClientID = 0
If Not IsDBNull(oRemoteRS("client_name")) Then
sClientName = oRemoteRS("client_name") Else sClientName = ""
oLocalCommand.Parameters.Clear()
oLocalCommand.Parameters.Add("@client_id",
SqlDbType.Int).Value = iClientID
oLocalCommand.Parameters.Add("@client_name",
SqlDbType.NVarChar).Value = sClientName
oLocalCommand.ExecuteNonQuery()
Loop While oRemoteRS.Read
End If

oRemoteRS.Close()


oRemoteConn.Close()
oLocalConn.Close()
MsgBox("finished pull replication")
End Function

... Write only set functions removed...
End Class

 

[Gary W. Smith]

As a follow up to my own posting I have found some odder problems. I
can connect to the SQL server on my development machine from any
machine on the network using query analyzer. If I modify the
application to access a dedicated SQL server (not the one on my dev
machine) it works fine from the emulated pocket pc but if I try to
access the SQL server on the development machine it fails. I'm not
sure why this is. I figure if any other workstation can access the
development environment then the emulated pocket pc should be able to
as well. I've tried using it by both machine name and IP address.

Any ideas

 

[Victor Espina]

I'm having the same problem and had posted the question here a couple of
time without any luck... hope someone can give us some clue about this
odd problem.

Victor Espina

 

[jp]

Hi Gary, hope you haven't pulled your hair out just yet. Hopefully,
i'll be able to be of some assistance. I must admit that i only
browsed your post, but after looking at your remote connection string I
have a feeling i know what your problem is.

I was having the same issue recently when i was trying to connect to a
named instance of SQL Server Express. My connection string looked
something like the following:

return @"Server=" + RemoteServerName + "\" + RemoteInstance + ";user id
= " + UserName + "; password = " + Password + "; database = " +
RemoteDatabaseName + ";";

It turns out that when this is executed in a normal desktop environment
a UDP broadcast occurs that identifies which port the named instance is
listening on. However, this broadcast doesn't make it out to the
network when running on the device (something about either the device,
ActiveSync, or possibly both not understanding UDP). Furthermore, if
you've poked around this newsgroup enough, you'll notice that plenty of
these connection issues end up being resolved by simply identifying and
using the correct port number to connect to the database server.

Therefore, I modified my connection string to look like this:

return @"Server=" + RemoteServerName + ", " + portNumber + ";user id =
" + UserName + "; password = " + Password + "; database = " +
RemoteDatabaseName + ";";

(notice that i have used portNumber now so that Server becomes
something like "Sever = serverName, 4567")

To identify exactly which port you are using you can use the following
process:
1. Right click on My Computer
2. Select Manage
3. Expand the Services and Applications Tree
4. Expand the SQL Server Configuration Manager Tree
5. Expand the SQL Server 2005 Network Configuration Tree
6. Select Protocols for SQLExpress

(At this point in time you should ensure that TCP/IP is enabled. if it
is not Enable it by right-clicking, but remember that you will have to
restart the server for the changes to take effect)

7. Right click TCP/IP and choose properties
8. Select the IP Addresses tab
9. I've chosen to use dynamic ports, so all of the TCP Dynamic Ports
are set to 0 except for the last one in IPALL. There it has defaulted
to 1026 which is good for me and the one that I am using. If you make
a different decision, just ensure that you identify at this point which
port number you are listening to.

Finally, ensure that your connection string in code is modified to use
the port number you identified.

That exhausts my understanding of the problem at this point in time.
Hope it helps, and happy coding.

- jimmy

 

[Paul G. Tobey [eMVP]]

ActiveSync doesn't forward UDP packets. It only sends TCP packets through.

Paul T.

 

[jp]

Hi Gary, hope you haven't pulled your hair out just yet. Hopefully,
i'll be able to be of some assistance. I must admit that i only
browsed your post, but after looking at your remote connection string I
have a feeling i know what your problem is.

I was having the same issue recently when i was trying to connect to a
named instance of SQL Server Express. My connection string looked
something like the following:

return @"Server=" + RemoteServerName + "\" + RemoteInstance + ";user id
= " + UserName + "; password = " + Password + "; database = " +
RemoteDatabaseName + ";";

It turns out that when this is executed in a normal desktop environment
a UDP broadcast occurs that identifies which port the named instance is
listening on. However, this broadcast doesn't make it out to the
network when running on the device (something about either the device,
ActiveSync, or possibly both not understanding UDP). Furthermore, if
you've poked around this newsgroup enough, you'll notice that plenty of
these connection issues end up being resolved by simply identifying and
using the correct port number to connect to the database server.

Therefore, I modified my connection string to look like this:

return @"Server=" + RemoteServerName + ", " + portNumber + ";user id =
" + UserName + "; password = " + Password + "; database = " +
RemoteDatabaseName + ";";

(notice that i have used portNumber now so that Server becomes
something like "Sever = serverName, 4567")

To identify exactly which port you are using you can use the following
process:
1. Right click on My Computer
2. Select Manage
3. Expand the Services and Applications Tree
4. Expand the SQL Server Configuration Manager Tree
5. Expand the SQL Server 2005 Network Configuration Tree
6. Select Protocols for SQLExpress

(At this point in time you should ensure that TCP/IP is enabled. if it
is not Enable it by right-clicking, but remember that you will have to
restart the server for the changes to take effect)

7. Right click TCP/IP and choose properties
8. Select the IP Addresses tab
9. I've chosen to use dynamic ports, so all of the TCP Dynamic Ports
are set to 0 except for the last one in IPALL. There it has defaulted
to 1026 which is good for me and the one that I am using. If you make
a different decision, just ensure that you identify at this point which
port number you are listening to.

Finally, ensure that your connection string in code is modified to use
the port number you identified.

That exhausts my understanding of the problem at this point in time.
Hope it helps, and happy coding.

- jimmy

 

[jp]

Hi Gary, hope you haven't pulled your hair out just yet. Hopefully,
i'll be able to be of some assistance. I must admit that i only
browsed your post, but after looking at your remote connection string I
have a feeling i know what your problem is.

I was having the same issue recently when i was trying to connect to a
named instance of SQL Server Express. My connection string looked
something like the following:

return @"Server=" + RemoteServerName + "\" + RemoteInstance + ";user id
= " + UserName + "; password = " + Password + "; database = " +
RemoteDatabaseName + ";";

It turns out that when this is executed in a normal desktop environment
a UDP broadcast occurs that identifies which port the named instance is
listening on. However, this broadcast doesn't make it out to the
network when running on the device (something about either the device,
ActiveSync, or possibly both not understanding UDP). Furthermore, if
you've poked around this newsgroup enough, you'll notice that plenty of
these connection issues end up being resolved by simply identifying and
using the correct port number to connect to the database server.

Therefore, I modified my connection string to look like this:

return @"Server=" + RemoteServerName + ", " + portNumber + ";user id =
" + UserName + "; password = " + Password + "; database = " +
RemoteDatabaseName + ";";

(notice that i have used portNumber now so that Server becomes
something like "Sever = serverName, 4567")

To identify exactly which port you are using you can use the following
process:
1. Right click on My Computer
2. Select Manage
3. Expand the Services and Applications Tree
4. Expand the SQL Server Configuration Manager Tree
5. Expand the SQL Server 2005 Network Configuration Tree
6. Select Protocols for SQLExpress

(At this point in time you should ensure that TCP/IP is enabled. if it
is not Enable it by right-clicking, but remember that you will have to
restart the server for the changes to take effect)

7. Right click TCP/IP and choose properties
8. Select the IP Addresses tab
9. I've chosen to use dynamic ports, so all of the TCP Dynamic Ports
are set to 0 except for the last one in IPALL. There it has defaulted
to 1026 which is good for me and the one that I am using. If you make
a different decision, just ensure that you identify at this point which
port number you are listening to.

Finally, ensure that your connection string in code is modified to use
the port number you identified.

That exhausts my understanding of the problem at this point in time.
Hope it helps, and happy coding.

- jimmy

 

[jp]

Hi Gary, hope you haven't pulled your hair out just yet. Hopefully,
i'll be able to be of some assistance. I must admit that i only
browsed your post, but after looking at your remote connection string I
have a feeling i know what your problem is.

I was having the same issue recently when i was trying to connect to a
named instance of SQL Server Express. My connection string looked
something like the following:

return @"Server=" + RemoteServerName + "\" + RemoteInstance + ";user id
= " + UserName + "; password = " + Password + "; database = " +
RemoteDatabaseName + ";";

It turns out that when this is executed in a normal desktop environment
a UDP broadcast occurs that identifies which port the named instance is
listening on. However, this broadcast doesn't make it out to the
network when running on the device (something about either the device,
ActiveSync, or possibly both not understanding UDP). Furthermore, if
you've poked around this newsgroup enough, you'll notice that plenty of
these connection issues end up being resolved by simply identifying and
using the correct port number to connect to the database server.

Therefore, I modified my connection string to look like this:

return @"Server=" + RemoteServerName + ", " + portNumber + ";user id =
" + UserName + "; password = " + Password + "; database = " +
RemoteDatabaseName + ";";

(notice that i have used portNumber now so that Server becomes
something like "Sever = serverName, 4567")

To identify exactly which port you are using you can use the following
process:
1. Right click on My Computer
2. Select Manage
3. Expand the Services and Applications Tree
4. Expand the SQL Server Configuration Manager Tree
5. Expand the SQL Server 2005 Network Configuration Tree
6. Select Protocols for SQLExpress

(At this point in time you should ensure that TCP/IP is enabled. if it
is not Enable it by right-clicking, but remember that you will have to
restart the server for the changes to take effect)

7. Right click TCP/IP and choose properties
8. Select the IP Addresses tab
9. I've chosen to use dynamic ports, so all of the TCP Dynamic Ports
are set to 0 except for the last one in IPALL. There it has defaulted
to 1026 which is good for me and the one that I am using. If you make
a different decision, just ensure that you identify at this point which
port number you are listening to.

Finally, ensure that your connection string in code is modified to use
the port number you identified.

That exhausts my understanding of the problem at this point in time.
Hope it helps, and happy coding.

- jimmy

 

[jp]

Hi Gary, hope you haven't pulled your hair out just yet. Hopefully,
i'll be able to be of some assistance. I must admit that i only
browsed your post, but after looking at your remote connection string I
have a feeling i know what your problem is.

I was having the same issue recently when i was trying to connect to a
named instance of SQL Server Express. My connection string looked
something like the following:

return @"Server=" + RemoteServerName + "\" + RemoteInstance + ";user id
= " + UserName + "; password = " + Password + "; database = " +
RemoteDatabaseName + ";";

It turns out that when this is executed in a normal desktop environment
a UDP broadcast occurs that identifies which port the named instance is
listening on. However, this broadcast doesn't make it out to the
network when running on the device (something about either the device,
ActiveSync, or possibly both not understanding UDP). Furthermore, if
you've poked around this newsgroup enough, you'll notice that plenty of
these connection issues end up being resolved by simply identifying and
using the correct port number to connect to the database server.

Therefore, I modified my connection string to look like this:

return @"Server=" + RemoteServerName + ", " + portNumber + ";user id =
" + UserName + "; password = " + Password + "; database = " +
RemoteDatabaseName + ";";

(notice that i have used portNumber now so that Server becomes
something like "Sever = serverName, 4567")

To identify exactly which port you are using you can use the following
process:
1. Right click on My Computer
2. Select Manage
3. Expand the Services and Applications Tree
4. Expand the SQL Server Configuration Manager Tree
5. Expand the SQL Server 2005 Network Configuration Tree
6. Select Protocols for SQLExpress

(At this point in time you should ensure that TCP/IP is enabled. if it
is not Enable it by right-clicking, but remember that you will have to
restart the server for the changes to take effect)

7. Right click TCP/IP and choose properties
8. Select the IP Addresses tab
9. I've chosen to use dynamic ports, so all of the TCP Dynamic Ports
are set to 0 except for the last one in IPALL. There it has defaulted
to 1026 which is good for me and the one that I am using. If you make
a different decision, just ensure that you identify at this point which
port number you are listening to.

Finally, ensure that your connection string in code is modified to use
the port number you identified.

That exhausts my understanding of the problem at this point in time.
Hope it helps, and happy coding.

- jimmy

 

[Gary W. Smith]

Not sure why but this showed up multiple times.

I have indeed changed the port for the SQL connect a couple times.
Still no dice. It was originally set for 27858 on the dymanic port and
I changed it to 1433 and I also tried 5001.

I still cannot connect from the emulated environment but I can connect
from other remote workstations to the server so I know that the SQL
server is indeed working.

The work around is for me to just use one of the other development
servers on the network. I was trying to just keep it contained on the
development workstation (my laptop).

I will try your changes inn any evnet to see if that fixes my problem.

 

[Gary W. Smith]

I went back and maually set the port to 5189 (tries SQL port first then
I changed it to the dynamic port including SQL Server restarts). Here
is the current connection string.

oRemoteConn.ConnectionString = "server=10.0.54.2\sqlexpress,5189;user
id=MobileUser;password=password;database=dev_mobile"

Still no dice. This should be a simple problem but it's just not
playing nicely.