SqlCeRemoteDataAccess Pull issue with ConnectionString

I'm getting an error when attempting to pull some data from my SQL Server 2005 database.

It throwing an exception on my strSQLConn string with the following message (please replace <MyServer> with my actual server name:

Failure to open SQL Server with given connect string. [ connect string = Data Source=<MyServer>;Initial Catalog=MobileParts;Integrated Security=True ]

string localstrSQLConn = @"Data Source='\Program Files\CreateMobileTest\MobileParts.sdf';Password='';Max Database Size=128;Default Lock Escalation =100;";

string InternetUrl = @"http://<MyServer>/zTestWebSite/MobileParts/sqlcesa30.dll";

SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(InternetUrl, localstrSQLConn);

string strSQLConn = "Data Source=<MyServer>;Initial Catalog=MobileParts;Integrated Security=True";

try

{

rda.Pull("QtyRemaining", "Select * from Transaction_Data_Archive", strSQLConn);

}

catch (Exception ex)

{

MessageBox.Show("Error: " + ex.Message);

}

This is the exact (copy and paste) connection string I use for any other connection to the SQL Server from Windows Apps. FYI, Synching through IIS works without issues.

Is there something else I should be adding/taking away from the localConnectionString for a pull Or is this one of those errors that is actually caused by something else

Answer this question

SqlCeRemoteDataAccess Pull issue with ConnectionString

ccoxon
Okay - got passed authentication. Now the error is:

Error: Failure to open SQL Server with given connect string. [ connect string = Data Source=<Server Name>; Initial Catalog=MobileParts;Integrated Security=True].

Wait, that's my original issue isn't it So now that we are getting passed authentication, why should my SQL Server connection string work perfectly in a Windows Form app, but getting an error here
dor
Thx to Google (our Programmer's friend), I was able to eventually find the Integrated Security = "SSPI" addition to my Remote Connection String... This works and is now allowing me to push/pull/submitSql.
Celtic_Guy

one problem I see is that you are specifying integrated security but not providing a userid or password in the remote connection string.

Darren
rwbogosian
Hi Darren - thx for responding to my posts. I can see you've given great advice to others.

I'm currently running locally with all my tools(sql, vs, pda emulation, iis) all on the same box. Are we talking a domain login Because I tried my domain login which has full rights anywhere in the state of Washington :) , and I still can't authenticate. I tried IUSR_<Server Name> and password with no results.

I am able to sync with the following SQLCeReplication object:

repl.InternetUrl = @"http://<server name>/zTestWebSite/MobileParts/sqlcesa30.dll";

repl.Publisher = @"<server name>";

repl.PublisherDatabase = @"MobileParts";

repl.PublisherSecurityMode = SecurityType.NTAuthentication;

repl.Publication = @"MobileParts";

repl.Subscriber = @"MobileParts";

repl.SubscriberConnectionString = @"Data Source='" + filename + "';Password='';Max Database Size=128;Default Lock Escalation =100;";

What part of this requires authentication the SQL DB The IIS

-- Wait - hold the phone, I think I may have it....
dh

I get the same problem allso. "Failure to open SQL Server with given connect string..... Integrated security=SSPI"

I am actually trying to pull data from a SQL Server with windows authentication to a PPC. However since I don't have access to change things on the server with the main SQL server I set up IIS on my own laptop and have my PPC connect to that IIS. I have set it up so that anonymous users that gain access to the IIS are being logged in under my own account which has access to the sql server database. I am totally at a loss but it is important for me to find a solution. Any help would be appreciated.. Thanks
extraT

correction on my last post - you need to set the InternetLogin and InternetPassword on the RemoteDataAccess object in order to use Integrated Security.

Darren
Azeem Fraz
There could be a number of reasons authentication is not taking place - sometimes it truly seems like Mars must line up with Saturn, but only on Tuesdays. From the DB to the publication, the IIS configuration, the subscription and the connection object settings - all must be coordinated as MS has simply locked everything down, as they should. That is probably why MS gurus are hesitant to respond to these type of question - it simply could be so many different causes. All I can say, is that SSPI was my final fix, BUT, everytime I change the database structure and sync all the way through, I still end up spending a few hours fixing connectivity issues again. Sorry I can't be more help than that. Heads up, keep working through it - it's why we get paid so doggone much, eh
Mar G
haha I agree
well I spent alot of time doing this and as you said everytime I get a different error message

thanks for your reply, its really appreciated

cheers
Brian Travis
I have exactly the same problem. however I tried changing "Integrated Security = true" to "=SSPI" however it's still not working

could tell us how did you fix the problem exactly

thanks
SqlCeRemoteDataAccess Pull issue with ConnectionString

Answer this question