Solved

Write Access denied after replicating DB in Microsoft SQL Server 2012

Posted on 2014-12-04
11
117 Views
Last Modified: 2014-12-28
I set up Replication in MS SQL Server 2012 Standard edition by using the standard create distributor, create publisher, new publication, new subscriber method in MSSMS. Before I added a DB to be published, it was accessible by my application. As soon as I publish the DB for Replication (Merge), my application says 'write access denied'. I'm using the same username to connect to the DB, nothing really changed other than marking the DB for publication.

What can I do to enable Read/Write access to this DB after publication? I cannot for the life of me figure out what combination of permissions I need, etc. I've tried connecting as a SQL user (sa) with full permissions plus many other things. The DB properties say False when checking the Read Only status.

Any help is greatly appreciated!

Thanks,

Chris
0
Comment
Question by:metricroofing
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40482536
Did you check if a snapshot was running in the meanwhile?
Also, it's an application error and that can be very generic message error. Did you check for errors in SQL Server log?
0
 

Author Comment

by:metricroofing
ID: 40483089
Thanks for your reply.  It's a small test DB and the snapshot finished very quickly. Also, I checked the SQL server log and there are no errors when making the connection to the DB. I checked both my local event log and the server's event log for Security events that might be indicative of what's happening. Strange thing is, the login events are the same as when the DB is not replicated (same UN, etc). I spoke wiht my applications support team and they referenced the Locking table and advised me to clear that and it should be read/write but there was no data in the locking table.

The only thing I can see different is the addition of the rowguid column to the DB tables. I'm not sure why that could be preventing write access though.

Thanks!
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40483136
Yes, sounds like a lock issue. During the Snapshot locks may occur but since you said that finished very quickly then should be something else that originates the locks.
You can run a SQL Profiler to audit what's happening in the database. You may find some interesting things with a trace.
Can't think in another solution by now.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:metricroofing
ID: 40483154
Ok, so a SQL profiler, should I run that while assigning the DB as a publication or just during my attempts to connect to the DB after I've assigned it as a publication? Thanks!
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40483169
I would say that you'll only need to run in during the attempts to connect since it's when the error occurs.
0
 

Author Comment

by:metricroofing
ID: 40483327
OK, I have run the SQL Trace on my connection to the DB. I have found out some interesting things. Hopefully you can help with these new findings?

So, the trace identified an attempt to write to the locking table but appears to not actually write to it. I then, disabled publication of the DB and ran a trace then. I show the table being written to and then the row being deleted which seems to indicate a successful write. Now, I have narrowed this problem down to the addition of the rowguid coumn in this or all of the tables. When I view the INSERT statement via SQL trace, I'm showing that the statement does not include defined column names. Upon reading the Merge replication details, it says that INSERT statements will fail if column defs are not included in INSERT statements. So, I believe that's my problem.

The developer will most likely not adjust their code for us to include column defs in all their insert statements. Is there any way I can adjust the tables or DB to somehow make this happen myself?? As far as I can tell stored procedures are not used.

Thank you!
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40486276
I'm still wondering why it locks the table if the replication is running. Could you understand why it's having these behavior?
0
 

Author Comment

by:metricroofing
ID: 40486845
I don't think it's so much locking the table against replication. The replication part works fine. I was able to create a subscriber and the whole DB replicated via the snapshot and I can access it. It's just when the application tries to access to the DB that I get "read only".  I spoke with the application dev and they said the Locking table is the first to be test written to when connecting the the DB. It's supposed to ensure no one else is connecting at the very same moment or something like that. Like I said above, after running a Trace, I found the application does not provide column definitions in it's INSERT statements. Replication is said to fail if column defs are not included. I'm just wondering if there's a way I can manipulate the DB tables or similar to avoid this issue while still getting replication.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40488410
I found the application does not provide column definitions in it's INSERT statements.
Yeah, this isn't a good thing to do.

I'm just wondering if there's a way I can manipulate the DB tables or similar to avoid this issue while still getting replication.
Without changing the application? I don't think so.
0
 

Accepted Solution

by:
metricroofing earned 0 total points
ID: 40515379
This ended up being an application restriction, not a problem with MSSQL Replication.
0
 

Author Closing Comment

by:metricroofing
ID: 40520294
Application was limited in it's SQL coding, did not pass coumn headers with select statements thus rendering itself in compatible with the newly created column that replication installs.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question