This is a great tutorial and it's a shame one of the more important steps was missed.
In the “Create the snapshot user” section you you find the steps to create the snapshot_agent account. Then in the “Create the snapshot folder” section you find the share and folder permissions. However, at no point do the instructions advise you about adding the snapshot_agent to the SQL Server Logins. The result is that agent cannot perform the initial snapshot but you won't find this out until 50 steps later after Step 10 in the section “Create a new subscription".
To get back on track, openthe Object Explorer's Security section and add the snapshot_agent to your logins. Then using the "User Mappings", set an appropriate level for the SQLMobile database role. Once completed you then need to run the agent.
Right-click the SQLMobile publication you created and select "View Snapshot Agent status". From that dialog you can select "Start" to run the agent. When it completes, you can return to the tutorial section "Create a new subscription" and continue with the tutorial.
that's one way to do it and thanks for pointing out the omission. a more common approach is to make sure the account that the snapshot agent runs as is granted permissions on the publication, the database engine, and the database.
Darren
|||
Darren,
In order to grant permissions on the database engine you need to create the login. And just granting permission on the database engine, database and the publication doesn't seem do it. You still need to asign a "Server Role" to the login. My first guess was "processadmin"; however, I tried a number of different combinations without success. Only when I set the snaphot-agent to the role of "sysadmin" was I able to get the agent to complete the process of creating the initial snapshot.
|||
that's true if the account you chose to run SQL Agent as isn't already recognized in the sysadmin role as a SQL Server login. for the average developer trying to get merge repl working the first time, SQL Server and IIS are both running on the machine that the device is connected to via ActiveSync. what I was trying to say in my last post is, whatever account you log on to you machine with, as long as it is an Administrator account, this is a good account to run the SQL Agent under. That makes the permissions issues easier to configure on SQL Server as you only have to grant that login appropriate permissions on the pub and the db. Of course you also grant permissions to the IUSR_{your machine name} account if using anonymous auth.
in a production environment, some other account should be used and as you correctly noted, this account needs to either be sysadmin on SQL Server or be granted db_reader and db_writer on the pub and published database. typically, IIS and SQL Server are on separate boxes in this scenario and a domain account is used that both machines can recognize.
Darren
|||
It is less than practical to use the "sysadmin" role in this instance. Review of the documentation shows that the minimum permissions for a "pull subscription" require the login to be associated with a user in the distribution database. At minimum be a member of the db_owner fixed database role in the distribution database.
So I have now removed the sysadmin role from the computername\snapshot_agent and added it as a user in the distribution database with the db_owner role. These permissions allow the agent to run the replication snapshot job.
Now, I have no indication that others are able to run this tutorial without specifically setting the above permission. If they are, then perhaps some other settings related to the wizards or replication itself are necessary.
The real point here is that given a tutorial which identifies each step in setting up replication (including specific names and permisions) should work according to the names/permissions identified. So lets fix the omission and move on.
Note: specific infornation is available at http://msdn2.microsoft.com/en-us/library/ms151868(d=ide).aspx
