Authentication with MSSQL 2005 Mobile Merge Replication

Hi,
I'm using MSSQL 2005 Mobile subscriber to partitioned publication (with
merge replication). In my case the partition is security-related, so it's
important to ensure that different subscribers will get only their own data.
Since I can't use SUSER_NAME(), and since HOST_NAME() can be overriden, what
are the options to authenticate the user properly?
Thanks,
Vladimir K.
You can use suser_name, it maps to publisherlogin, I also don't quite
understand what you mean by host_name can be overridden - it can be
overridden by the hostname parameter in your merge replication class
(SQLCeReplication) to define whatever you want, which could be the account
name you wish to filter by.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vladimir Kofman" <vladimir.kofman@.gmail.com> wrote in message
news:u9lqmIRSHHA.4832@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm using MSSQL 2005 Mobile subscriber to partitioned publication (with
> merge replication). In my case the partition is security-related, so it's
> important to ensure that different subscribers will get only their own
> data. Since I can't use SUSER_NAME(), and since HOST_NAME() can be
> overriden, what are the options to authenticate the user properly?
> Thanks,
> Vladimir K.
>
|||In SQL Mobile suser_name doesn't work for me... And the problem with
host_name is exactly as you stated: anyone can define anything, so it seems
I don't have an option to define partitions based on some security setting:
let's say I have two partitions A and B, and have two users a and b
appropriately. How do I prevent from user a to synchronize with partition B?
(and from user b with A?)
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uMU67pSSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> You can use suser_name, it maps to publisherlogin, I also don't quite
> understand what you mean by host_name can be overridden - it can be
> overridden by the hostname parameter in your merge replication class
> (SQLCeReplication) to define whatever you want, which could be the account
> name you wish to filter by.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Vladimir Kofman" <vladimir.kofman@.gmail.com> wrote in message
> news:u9lqmIRSHHA.4832@.TK2MSFTNGP04.phx.gbl...
>
|||how about using certificates mapped to nt accounts. This way they will have
to know the password of the account you are going to pull with. The
communication will be encrypted and you can filter on suser_name which maps
to the publisherlogin.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vladimir Kofman" <vladimir.kofman@.gmail.com> wrote in message
news:endEvgVSHHA.1228@.TK2MSFTNGP06.phx.gbl...
> In SQL Mobile suser_name doesn't work for me... And the problem with
> host_name is exactly as you stated: anyone can define anything, so it
> seems I don't have an option to define partitions based on some security
> setting: let's say I have two partitions A and B, and have two users a and
> b appropriately. How do I prevent from user a to synchronize with
> partition B? (and from user b with A?)
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uMU67pSSHHA.2212@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks for your replies
But can you elaborate a bit more on the option you've suggested?
Thanks again.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eLdVCrVSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> how about using certificates mapped to nt accounts. This way they will
> have to know the password of the account you are going to pull with. The
> communication will be encrypted and you can filter on suser_name which
> maps to the publisherlogin.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Vladimir Kofman" <vladimir.kofman@.gmail.com> wrote in message
> news:endEvgVSHHA.1228@.TK2MSFTNGP06.phx.gbl...
>
|||You have to configure the web serve to accept client certificates. This was
supported in SQL CE 2.0, it appears to be supported in Web Synchronization
as well, but it is not clear from the documentation that it is.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vladimir Kofman" <vladimir.kofman@.gmail.com> wrote in message
news:OL9wDOWSHHA.3948@.TK2MSFTNGP05.phx.gbl...
> Thanks for your replies
> But can you elaborate a bit more on the option you've suggested?
> Thanks again.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eLdVCrVSHHA.2212@.TK2MSFTNGP02.phx.gbl...
>
sql

Authentication error in SQL Mobile

Hi ,

I was trying to run my application that has to Synchronize data between sql mobile and sql 2005. In the emulator all is fine and executes without any error.But when I deploy the application in real device it throws an exception:"Authentication failed in server running IIS". The device is WM5.0

The connection string is like this.

String strRemote = "Provider=SQLOLEDB;Data Source=servername;User ID=userid;Password=password;Initial Catalog=databasename";

I am getting the exception only when the application is deployed in the device.

The error log in C:inetpub/wwwroot/Sync is the following:

2007/05/25 12:54:29 Hr=00000000 SQLCESA30.DLL loaded 0
2007/05/25 12:54:29 Hr=80070585 ERR:REQUEST NOT QUEUED for ulRSCBId = -1
2007/05/25 12:54:58 Hr=80070585 ERR:REQUEST NOT QUEUED for ulRSCBId = -1
2007/05/25 12:57:00 Hr=80070585 ERR:REQUEST NOT QUEUED for ulRSCBId = -1
2007/05/25 13:00:37 Hr=80070585 ERR:REQUEST NOT QUEUED for ulRSCBId = -1
2007/05/25 14:55:07 Hr=00000000 SQLCESA30.DLL loaded 0
2007/05/25 14:56:10 Thread=1534 RSCB=2 Command=SCHK Hr=80040E2F The statement has been terminated. 3621
2007/05/25 14:56:10 Thread=1534 RSCB=2 Command=SCHK Hr=80040E2F Violation of PRIMARY KEY constraint 'PK_Orders'. Cannot insert duplicate key in object 'dbo.Orders'. 2627
2007/05/25 19:19:20 Hr=00000000 SQLCESA30.DLL loaded 0
2007/05/25 19:19:20 Hr=80070585 ERR:REQUEST NOT QUEUED for ulRSCBId = -1
2007/05/25 19:20:12 Hr=80070585 ERR:REQUEST NOT QUEUED for ulRSCBId = -1
2007/05/25 19:21:27 Hr=80070585 ERR:REQUEST NOT QUEUED for ulRSCBId = -1
2007/05/25 19:21:47 Hr=80070585 ERR:REQUEST NOT QUEUED for ulRSCBId = -1

Please reply....

It seems to be authenticating fine.

It looks like the error is a primary key constraint on the Orders table.

Just a first look,

Adamus

Auhtentification problem with Remote Data Access

Hello

I try to make a pull operation from an SQL SERVER 2005 database to a SQL Mobile 2005. I set Integrated authentification on IIS and SQL SEvrer authentification in the database. It passes the IIS authentification, but not the sql Server one. In addition, I tried to connect directlly to the sql with the same connection string anf it works.

Can you help me with this? Do i have to extra configure that dll(the agent) in the virtual directory, or sometihng llike that?

This is my code:

// Connection String to the SQL Server

//

string rdaOleDbConnectString = "Data Source=Sql07;Initial Catalog=AdventureWorks; " +

"User Id=sa;Password = sql250";

SqlConnection conn = new SqlConnection(rdaOleDbConnectString);

conn.Open();

// Initialize RDA Object

//

SqlCeRemoteDataAccess rda = null;

try

{

// Try the Pull Operation

//

rda = new SqlCeRemoteDataAccess(

"http://192.168.0.79/SqlMobileReplication/sqlcesa30.dll",

"assolutions\\dan.popescu",

"password",

"Data Source=\\my documents\\Baza.sdf");

rda.Pull("Employees", "SELECT * FROM DimEmployee", rdaOleDbConnectString,

RdaTrackOption.TrackingOnWithIndexes, "ErrorTable");

// or, try one of these overloads:

//

// rda.Pull("Employees", "SELECT * FROM DimEmployee", rdaOleDbConnectString,

// RdaTrackOption.TrackingOnWithIndexes);

//

// rda.Pull("Employees", "SELECT * FROM DimEmployee", rdaOleDbConnectString);

}

catch (Exception ex)

{

// Handle errors here

//

}

finally

{

// Dispose of the RDA object

//

rda.Dispose();

}

You can see that I have tried the connection first, and it really works. Isn't it the same thing it's doing at replication?

What is the error you are getting?
Also are you sure that the server has the table DimEmployee in the database?

Auhtentification problem with Remote Data Access

Hello

I try to make a pull operation from an SQL SERVER 2005 database to a SQL Mobile 2005. I set Integrated authentification on IIS and SQL SEvrer authentification in the database. It passes the IIS authentification, but not the sql Server one. In addition, I tried to connect directlly to the sql with the same connection string anf it works.

Can you help me with this? Do i have to extra configure that dll(the agent) in the virtual directory, or sometihng llike that?

This is my code:

// Connection String to the SQL Server

//

string rdaOleDbConnectString = "Data Source=Sql07;Initial Catalog=AdventureWorks; " +

"User Id=sa;Password = sql250";

SqlConnection conn = new SqlConnection(rdaOleDbConnectString);

conn.Open();

// Initialize RDA Object

//

SqlCeRemoteDataAccess rda = null;

try

{

// Try the Pull Operation

//

rda = new SqlCeRemoteDataAccess(

"http://192.168.0.79/SqlMobileReplication/sqlcesa30.dll",

"assolutions\\dan.popescu",

"password",

"Data Source=\\my documents\\Baza.sdf");

rda.Pull("Employees", "SELECT * FROM DimEmployee", rdaOleDbConnectString,

RdaTrackOption.TrackingOnWithIndexes, "ErrorTable");

// or, try one of these overloads:

//

// rda.Pull("Employees", "SELECT * FROM DimEmployee", rdaOleDbConnectString,

// RdaTrackOption.TrackingOnWithIndexes);

//

// rda.Pull("Employees", "SELECT * FROM DimEmployee", rdaOleDbConnectString);

}

catch (Exception ex)

{

// Handle errors here

//

}

finally

{

// Dispose of the RDA object

//

rda.Dispose();

}

You can see that I have tried the connection first, and it really works. Isn't it the same thing it's doing at replication?

What is the error you are getting?
Also are you sure that the server has the table DimEmployee in the database?