5

It appears here: 

            SyncAdapter notesSyncAdapter = notesBuilder.ToSyncAdapter();
        ((SqlParameter)notesSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_last_received_anchor"]).DbType = System.Data.DbType.Binary;
        ((SqlParameter)notesSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_new_received_anchor"]).DbType = System.Data.DbType.Binary;

The user has has all permissions on both the table, schema and the database. Any idea what might this still throw this exception?

more details: SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();

builder["Data source"] = "achernar"; builder["User ID"] = "sbbf974"; builder["Password"] = "whatever"; builder["Integrated Security"] = false; builder["Initial Catalog"] = "sticky";

user228137
  • 762
  • 1
  • 16
  • 34
  • Some suggestions here: http://stackoverflow.com/questions/1196793/the-select-permission-was-denied-on-the-object-address-database-cnet-85731 – Ben Griswold Aug 10 '10 at 14:57
  • skimmed through it.Thanks. more details: SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder(); // 1. Prepare server db connection and attach it to the sync agent builder["Data source"] = "achernar"; builder["User ID"] = "sbbf974"; builder["Password"] = "whatever"; builder["Integrated Security"] = false; builder["Initial Catalog"] = "sticky"; – user228137 Aug 10 '10 at 15:05
  • Where does SQL Lite come into all this? – leppie Aug 11 '10 at 18:03
  • following the first demo showcased on syncguru.com, my attempt is to sync an sqlite db with sql server 2008 – user228137 Aug 12 '10 at 08:49

4 Answers4

3

The issue was with the user, as Abe guessed. The user was dbo, meaning that although it should have had all the permissions, it didn't. So what I've done was to create another login, to which I gave the following database roles: public, db_datareader, db_datawriter. It works now.

user228137
  • 762
  • 1
  • 16
  • 34
  • Please accept your own answer once it becomes available, so that this question is marked as resolved. Glad you found a solution - I've never heard of the DBO user not having rights to do everything. – SqlRyan Aug 11 '10 at 22:52
1

Are you absolutely positive that your code and database are synced up correctly? If you can i'd recommend having one of your coworkers check the following. Sometimes a fresh set of eyes can catch something you've been overlooking...

  • Are you referring to the correct DB in your code (as opposed to a development DB)
  • Are you connecting as the user you think in your code
  • Does that user have permissions in the DB you are connecting to

I know it probably sounds obvious but I'd say it's worth having someone else double check for you. I know I've been caught by this before...

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • yes. I've tested the credentials/settings by using the wizard that pops when adding a new LocalDataCacheItem. – user228137 Aug 10 '10 at 15:35
0

Who is the 'user' ? I much rather trust SQL Server that clearly states that current connection does not have permission. The sync framework goes through several layers, via IIS and ISAPI until it reaches the database, so you better figure out exactly what credentials are used. Is there impersonation at IIS layer occurring? Constrained Delegation? Does the sync framework client authenticate with IIS using what (NTLM/Digest/Basic/Nothing) ?

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
0

Try deleting

Trusted_Connection=True;

or

Integrated Security=True;

from your C# code's connection string.

Jeremi
  • 1
  • 3