Remote Data Access Connection Testing

[Edward V. Dolikian]

I've been trying to get up to speed on Remote Data Access / Replication. It
seems that I have a "Server Side Connection or configuration" problem and
looking for ideas on how to isolate/correct the problem. At this point, I
am simply trying to use RDA and the Pull method to retrieve a data table and
its contents from the Server SQL database.

I did create an ASP.NET Windows application against the same SQL database on
the server and was able to populate a datagrid on my desktop application. I
double checked my connection settings paramters on the connection string on
my desktop (e.g. Provider, Data Source, User ID, Password) and they do match
my device settings (see code below).

I am able to test that the SQL Agent on my device is connecting to the
sscesa20.dll on my web server. When I navigate to the address, I do get a
response back.

Somewhere in between, the connection is not happening. I have attempted to
turn off / minimize security but no doubt this is where my problem lies.
Ultimately, I recongize that you must use security but for the time being, I
just want to get it working. Is there any way to debug / test / trace what
is happening on the server? Is there a command I can issue to ask me to
login so I can check if I am connecting that is more generic? How should
should SQL Server be configured for authenticaiton? I think I have it
configured to use SQL and Windows Authentication.

Any ideas / suggestions would be much appreciated.

Ed

I am using the sample in Rob Tiffany's book on SQL CE Database Development.
Here is a sample of my code. I have substituted / blanked out certain
fields but hopefully this gives you an idea. Net result is that I get a
blank database on my device with no tables and thus no data. I do get a
SQLCEExeption message box with no message. On subsequent attempts, I get an
error on the DROP Table statement because it wasn't created in the first
place.

Dim cn As SqlCeConnection
Dim rda As SqlCeRemoteDataAccess

Dim sqlengine As SqlCeEngine

Try

btnPull.Enabled = False

' Create Database if it doesn't already exist

If Not File.Exists("\My Documents\Intelligencedata.sdf") Then

sqlengine = New SqlCeEngine

sqlengine.LocalConnectionString = "Data Source=\My
Documents\IntelligenceData.sdf; Password=apress; Encrypt Database=true"

sqlengine.CreateDatabase()

sqlengine.Dispose()

Else

' Open the connection to the database

cn = New SqlCeConnection("Data Source=\My
Documents\IntelligenceData.sdf;password=apress")

cn.Open()

Dim cmd As SqlCeCommand = cn.CreateCommand

' Drop the FieldMemos table

cmd.CommandText = "DROP TABLE fieldmemos"

cmd.ExecuteNonQuery()

' Close the connection

If cn.State <> ConnectionState.Closed Then

cn.Close()

End If

End If

' Instantiate the RDA Object

rda = New SqlCeRemoteDataAccess

' Connection to the SQL Server

Dim remoteconnectionstring As String = "Provider=SQLOLEDB;Data
Source=SENSIBLE;Initial Catalog=IntelligenceData;User ID=sa;Password=xxxyyy"

rda.InternetUrl = "http://xx.yy.zzz.xx/ssce20/sscesa20.dll"

rda.LocalConnectionString = "Data Source=\My
Documents\IntelligenceData.sdf;SSCEatabase Password=apress"

rda.Pull("FieldMemos", "Select * from FieldMemos", remoteconnectionstring,
RdaTrackOption.TrackingOnWithIndexes, "FieldMemosErrorTable")

Catch Sqlex As SqlCeException

Dim sqlerror As SqlCeError

For Each sqlerror In Sqlex.Errors

MessageBox.Show(sqlerror.Message)

Next

Catch ex As Exception

MessageBox.Show(ex.Message)

Finally

rda.Dispose()

btnPull.Enabled = True

End Try

End Sub

 

[chris]

Try the following VB equivalent for your catch statement, it will show
more detail...


catch(System.Data.SqlServerCe.SqlCeException e)
{
SqlCeErrorCollection errorCollection = e.Errors;
string s = "";
foreach (SqlCeError err in errorCollection)
s += err.Message+"\n";
MessageBox.Show(s);
}

Also, check a couple of things...

1) Firewalls turned off

2) On device select Start->Settings->Connections->Connections->Advanced->Select
Networks and set both combo boxes to 'My Work Network'

3)Are you specifying the server by machine name or IP address, try IP
Address.

Are you connecting over Active Sync or LAN?

Chris

 

[Edward V. Dolikian]

Thanks for the input. The long and the short of it is that is still isn't
working. In short, here is what I did / found. Obviosuly any specifics you
can provide, reduces the number of variables and gets me closer to solving
the problem.

Windows firewall was on, turned off - didn't work.
-----------------
Added code to list out error (attempted to translate to vb.net from what I
know). Now all I get is a message "\n". Probably doing something really
dumb but tell me the vb equivelent if possible. It must have lost something
in the translation.

Dim s As String
s = ""
For Each sqlerror In Sqlex.Errors
s += sqlerror.Message + "\n"
MessageBox.Show(s)
Next

----------------

When I change both settings to Work Network, I get a message that I must
configure a proxy server. Not sure if I know all the addresses but I am
assuming that the Port 80 is the 192.168.1... address? and the WAN is my
external IP address being forwarded to my IIS (eg 68.61.xxx.xx). In
general, I would assume that I should be specifying my external address
(68.61.xx.yy). I typically would like to use a WiFi connection to access
the internet.

Lastly, I am a bit confused as to which Login account I should be using and
whether I should specify the InternetLogin or InternetProxyLogin. As a
test, I changed my user id and password to be incorrect and didn't get an
error message either. Should I be using by Windows Login (e.g.
administrator) or my SQL Administrator ID (e.g. sa) user / password.

I have not been testing on my device but have been using the emulator
(2003).

Here is my current RDA code to Pull the data...

rda.InternetLogin = "sensible\administrator"
rda.InternetPassword = "mypassword"
rda.InternetUrl = "http://68.61.yyy.xx/ssce20/sscesa20.dll"
' rda.InternetUrl = "http://192.168.1.20/ssce20/sscesa20.dll"
rda.LocalConnectionString = "Data Source=\My
Documents\IntelligenceData.sdf;SSCEatabase Password=apress"
rda.Pull("FieldMemos", "Select * from FieldMemos", remoteconnectionstring,
RdaTrackOption.TrackingOnWithIndexes, "FieldMemosErrorTable")
Catch Sqlex As SqlCeException
Dim sqlerror As SqlCeError

Thanks again

 

[Edward V. Dolikian]

Finally got it working! Not quite secure or total functional but was able
to make the connection and move data into my device. A lot of things aren't
working (namely any security or access from outside of my firewall but I've
got a starting point to work from). Thanks!

To save others some time, here are some settings that worked for Annonomous
access.

Leave Internetuser and Password as an Empty string (e.g....
rda.Internetlogin = string.empty)

Used my internal IP address of my desktop machine / IIS Server / SQL Server
(192.168.1.xx) Even though my firewall is turned off, I can't get it
working from my external address. I think this may have to do with
encription of the username and password when using basic authentication in
SQL. I think I need to switch to Windows authentication or SSL via a
certificate.

Used my SA account info in myRemoteconnection String. e.g.
"Provider=sqloledb;data source....;user id=sa,password=xxx" (Probably
because of the above)

Referred to my Data Source = myname\myname (I think the first specifies the
SQL Server Machine and the second refers to the Instance instead of just
Data Source = myname