Acessing SQL Server Named Instances from Compact Framework

jrthurler
jrthurler used Ask the Experts™
on
I would like to know if it´s now possible to access a named instance from my CF 3.5 application.

I´m trying access using this tip code:

using (SqlConnection conn = new SqlConnection("Data Source=10.120.184.70\SERVERSQL;Initial Catalog=CR;User Id=xx;Password=xx;"))

In my research on Google I found explanation that it´s not possible to do it using named instances, but I would like to know if there is another Service Pack or patch to fix this problem.

I´m using WM 6.5, C# and CF 3.5.

Regards,

JR
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It should work. Can you ping the server that you are trying to connect to?
are you sure you want to connect to sql server from your WM Device.
I hope your device is connected to sql server from everywhere, if it is then it must be having a wifi connection always.

Other approach is to write a wcf or web service and host it somewhere on internet.
Let the service talk to sql and you talk to service and get data in form of soap or Json...

Author

Commented:
BuggyCoder,

I´m using a connection to SQL Server when the device is attached to USB cable and using the host computer to comunicate with the SQL Server.

My app doens´t need to run on line, it works offline and just be online when attached to USB cable, just to transmit the data to my server.
can you connect to sql server from the host computer using the same connection string

Author

Commented:
Yes, it´s working perfectly! And If I connect to a SQL server without named instance it works fine too! The problem is with named instances!
just see if you can ping your dbserver from your ce device...

also try this as connection string:-

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;User ID=myDomain\myUsername;Password=myPassword;

i am using Trusted Connection here....

Author

Commented:
But in this connection string you are not using the named instance and that´s is my problem: my server is 10.1.1.100\Server. If I use the server without named instance, it´s works fine!
its a sample, change your ds name with yours.

but the issue that you are talking about here looks to be a network issue.
It has nothing to do with named instances or anything else. Try to ping your named instance from your ce device and if you cannot, ask your network team to help you....
hjgodeTier 3 Senior Technical Support Engineer

Commented:
You have to use integrated security. See http://stackoverflow.com/questions/4890287/connect-to-sql-server-named-instance-from-windows-ce

"...No there's no limitation on the CE connection. In fact the only thing different between the CE connection and the desktop is that a CE device can do something that a desktop can't. Pass the user name and password using Integrated Security.

Data Source=myServerAddress;Initial Catalog=myDataBase;
  Integrated Security=SSPI;User ID=myDomain\myUsername;Password=myPassword;

see Trusted Connection from a CE device

The problem is probably that the CE device can't see Timber\Test2 due to a network issue. Try Pinging Timber from your CE Device
..."
and
"...So i think the solution is to pass the username and password in the connection string since its not authenticated on the network. Wierd that you could connect to the default without being on the network but need it for a named instance..."

I also tested with Integrated Security=SSPI and it worked fine from CE (Windows Mobile device).

regards

Josef
hjgodeTier 3 Senior Technical Support Engineer

Commented:
If you cannot connect using Windows Authentication, try SQL Server integrated authentication. For example:
            //this works with sql authentication
string sConnection = @"Data Source=192.168.128.5\INTERMECC2008;Initial Catalog=Northwind;User ID=sqlce;Password=sqlce;Connection Timeout=15;";

replace IP, instance, user name and password with your environment.

regards

Josef

Author

Commented:
Josef,

did you tried the connection string provided by you with sucess on a Named Instance SQL Server ? If you take a closer look it´s almost the same connection string I´m using.

Just for remember, this connection string works fine if I use in a desktop computer but in the Compact Framework it doesn´t works, just with Named Instance. If I don´t use named instances it´s works fine.

Regards,

JR
Tier 3 Senior Technical Support Engineer
Commented:
Hello JR

YES, I did test the connection string(s). Both using WiFi and ActiveSync. Server is a 2003 Server running several SQL 2008 R2 instances.

Using ActiveSync or WindowsMobileDeviceCenter may cause a problem. Although you switched the connection to "This computer is connected to:"="The internet" it happens on my side, that the connect fails. I then have to switch to "This computer is connected to:"="Automatic" or back to "This computer is connected to:"="The Internet" to get it working.

Possibly any other setting is blocking the request on your side. But then only a view inside the Server Log or a network trace may help.

The below image was taken of an access try with wrong credentials (from the mobile device!)
SSPI error
So, please believe me, the CE SQL Client supports named instances.

~josef

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial