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 mode selection

There are two different tasks I would like to serve with SSEE, both case the users are changing seats. I think SQL Server Authentication would be better for lightweight user change but I have read everywhere that Windows Authentication Mode is the preferred way to go.

Is there any significant weakness in SQL Server authentication mode - security, work with stored procedures (CLR) or anything?

With SQL Server 2005, there has been a significant increase in the robustness of SQL Security. Especially if you 'enforce password policy' -including enforcing expiration policies on the SQL Accounts. (And your domain/AD has password policies defined and enforced.)

However, it the situation of users 'changing seats', Windows authentication 'should' still work fine.

|||

Before choosing Authentication mode there are few things to be considered. From the security point of view , Windows authentication is better. You can have all the security policy as in the OS level and user no need to remember multiple user name and password. In this case the main disadvantage is that it can not support multi OS platform. ie from UNIX machine you can not access this database. and many legacy software runs from multiplatform. This is the main reason we go for Mixed mode.

Madhu

|||

Hello.....

Best practice is to choose windows Authentication for SQL Server 2005 domain environment. If your user are using legacy application then better to go for Mixed Authentication.

No, there is nothing weak to choose mixed mode authentication. This type of authentication is needed for legacy applications. Application which are running on other platform(except Windows platform) and needs to connect to SQL Server.

authentication issues

Hi,
Our system auditor want to change the way in wich the
security on passwords is used on a sqlserver account:
password expiration, case sensitive, at least N characters
in length, the password must not be the same as the login,
passwords must have a lifetime...
but I don't know how I can do it when our applications can
not use Windows authentication
Can anyone help me?
ThanksHi,
In SQL Server authentication you do not have the facility to set password
policies.
Thanks
Hari
MCDBA
"Miriam" <anonymous@.discussions.microsoft.com> wrote in message
news:62e101c3e5ec$c3f08bf0$a401280a@.phx.gbl...

quote:

---

> Hi,
> Our system auditor want to change the way in wich the
> security on passwords is used on a sqlserver account:
> password expiration, case sensitive, at least N characters
> in length, the password must not be the same as the login,
> passwords must have a lifetime...
> but I don't know how I can do it when our applications can
> not use Windows authentication
> Can anyone help me?
> Thanks
>

---

|||Miriam,
Hari gave you the right answer.
You can, of course, write your own code to set passwords that will check for
comformity to rules and prevent others from using sp_password. If you did
this, you could create a table to record when the password was last set,
etc. I have done this sort of thing in the distant past, but would not do
it any more unless severely pressed.
A manual method would be to have the security admin set the passwords on SQL
Server accounts and keep a spreadsheet of when they were last changed. If
there are not too many of these the work should not be onerous.
Russell Fields
"Miriam" <anonymous@.discussions.microsoft.com> wrote in message
news:62e101c3e5ec$c3f08bf0$a401280a@.phx.gbl...

quote:

---

> Hi,
> Our system auditor want to change the way in wich the
> security on passwords is used on a sqlserver account:
> password expiration, case sensitive, at least N characters
> in length, the password must not be the same as the login,
> passwords must have a lifetime...
> but I don't know how I can do it when our applications can
> not use Windows authentication
> Can anyone help me?
> Thanks
>

---

|||Hi, Hari
Do you know about a Microsoft document where they have
wrote about?
I only found a table (MS SQL Server 2000 System
Administration book, Chapter 10)"Security Capabilities of
Windows Authentification Versus SQL Server
Authentication", but that document has not been sufficient
for the auditors
Could you help me with another reference?
Thanks

quote:

---

>--Original Message--
>Hi,
>In SQL Server authentication you do not have the facility

---

to set password

quote:

---

>policies.
>
>Thanks
>Hari
>MCDBA
>
>"Miriam" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:62e101c3e5ec$c3f08bf0$a401280a@.phx.gbl...
characters[QUOTE]
login,[QUOTE]
can[QUOTE]
>
>.
>

---

Auotincrement without using autoincrement

What do you have against using IDENTITY in this case? The trouble is that
without it you will have to serialize every INSERT, which is likely to be
unacceptable in a multi-user system.
David Portas
SQL Server MVP
--David Portas wrote:
> The trouble is that
> without it (IDENTITY) you will have to serialize every INSERT, which is li
kely to be
> unacceptable in a multi-user system.
It is not really multi-user in the sense you may be thinking. It is a
system which accepts FTP transfers and performs tasks on the received
files. The FTP service I wrote is multi-threaded. Problem is that many
automated clients all like to set up their transfers to run at 1 AM, for
example, so depending on how accurate everyone's clocks are, and how
similar the transmission files are in size, I can easily get 3 additions
made at almost the same time. When there may only be 30-100 additions
total per 24-hour period. I don't need it to be fast, but rather, just
am trying to avoid the primary key constraint issue, if I can, without
error. Obviously I could "try again" if I get an error first time around
from something like
INSERT INTO NMJobTransfers
(WorkID,TransferID,JobID,FileCount,ByteC
ount,Comments)
SELECT MAX(WorkID)+1, 99, 0 ,0 , 0, NULL FROM NMJobTransfers
BUT TO BE SURE I UNDERSTAND your implication, you are saying the above
approach is still going to open a very small window of time where there
could be a duplicate key error on the WorkID primary key column? Is that
correct?
- Lee|||If you set transaction isolation SERIALIZABLE you won't get a duplicate.
What you will get is blocking of simultaneous INSERTs. That is exactly the
problem that the IDENTITY feature is designed to solve. And that's why I
asked why you aren't using IDENTITY to do this.
David Portas
SQL Server MVP
--|||Actually, I am pretty sure that even using serializable you have to do
tricks with locks to make sure that there is no chance of two people getting
the same value because the read is done first, then the insert, so if two
users start looking for the max(nextval) at the same time, they both succeed
unless you use hold exclusive locks on the max value.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:wIadnZ2dnZ1RIDm4nZ2dnWKlZN-dnZ2dRVn-zZ2dnZ0@.giganews.com...
> If you set transaction isolation SERIALIZABLE you won't get a duplicate.
> What you will get is blocking of simultaneous INSERTs. That is exactly the
> problem that the IDENTITY feature is designed to solve. And that's why I
> asked why you aren't using IDENTITY to do this.
> --
> David Portas
> SQL Server MVP
> --
>