Merge Replication: Database Security Issue at Subscriber RRS feed

  • Question

  • Hello All,

    I have been working on merge replication. The set up is up but now I am facing some security concerns which clearly puts databse at risk at subscriber side.
    First I would give brief set up info.

    Publisher and Distributer: SQL SERVER 2008 Standard Edition I will say machine A
    Subscriber: SQL EXPRESS EDITION I will say machine B
    SubscriptionType: Pull (merge agent runs at subscriber)
    Since this machine would be used by workers and away from office location.
    Actually it can be in hands of everyone so database at subscriber becomes vulnerable.

    The workers log in to subscriber machine using DomainName/Userid having limited priviledges.

    My questions is regarding safety of my db which should not be taken away by any means what so ever
    1. DomainName/Userid uses front end and communicates with db.
    Note: here my application code would authenticate for db connection.

    Apart from point 1 their should not be any means of reaching to db or copying db.
    Potential accessing means for databases can be.
    1. Through Management studio.
    2. Through command prompt.
    Note: I got some views from Hilary Cotter and YoYo Yu about that. Thankyou for directing me but still I am not sure of safety.
    3. Copying database somehow.
    Note: please tell also if their are still some others ways of accessing/copying db from above 3 points which I havenot thought of.

    Please tell how generally databse is kept secured at subscriber machine.


    Thursday, July 29, 2010 6:56 PM

All replies

  • saandii777,

    Assign roles for the user that access the database.  First off, are the workers accessing the database from an application? if so what user is the application using?

    if not, what role are you assigning for the domain\username to your database?

    Thursday, July 29, 2010 7:34 PM
  • Thanks Wissam,

    Application connects to db through domain\username which would have a datareader&datawriter role.

    My concern is I must be in a situation of not allowing worker to get to
    1. Management Studio.   2. Sqlcmd.

    or any other means of accesing/copying of db other than through application.


    Thursday, July 29, 2010 8:07 PM
  • saandii777

    You can do tremendous things with security under your OS. As for your concern, my understanding is that the app uses a db_datareader/writer role that is completely separate from the worker's domain user. Taking that into account, the wokers' domain user will not be able to authenticate via management studio if they have limited (user group) on the machine. So is the case with SqlCmd. SQLCMD requires elevated privileges to execute.

    you can even make sure that the workers' domain user is not granted any access on the database from management studio.

    Another thing worth doing is to navigate to the  .mdf/.ldf folders and make sure you assign NTFS permission to only admin group and sql service user.

    Thursday, July 29, 2010 8:21 PM
  • Thanks Wissam,

    I pretty much understood and my application works the way you told.

    But the problem is that I run the merge agent at subscriber i.e workers laptop.

    I have 2 options for running merge agent
    1. RMO programming(can write in windows based application)
    2. Batch File.

    The files contain clear text sql account credentails to connect to subscriber/publisher.

    I am little apprehensive as this may be exposed or not.

    I am newest new in doing windows based application.
    Please tell me one thing if I have a form1.cs on which at button click event I write code for running agent.

    I publish that window application.
    and setup I install on workers laptop.

    Would there be any possiblity for them to know what form1 contains.
    I stand at the ground on my knowledge on windows based application.

    Note: when you run merge agent through any of above utility it creates a file replmerg.log
    which exposes all your info of publisher/subscriber instance name , db name login used everything but password field just becomes ****
    So is it wise to allow worker to run the agent through form based application or an admin should run from the laptop.

    My boss actually said that workers may not return to office say for a week.
    So they should be in position to make the sync by themselves.


    Thursday, July 29, 2010 10:11 PM
  • saandii7777

    If the workers are computer savvy they can certainly read the code in your exe or dll file even when they don't have the actual source code.

    Put it simply, here's what you can do: write your form and have it run the merge agent (you don't have to use RMO for that, but you still could) all you need is to call the merge command line.

    Taking into consideration a tight security you can write the password in your .config file and encrypt it or you can simply obfsucate all your source code and in that way it will be quasi impossible to crack it and read the contents.

    It is good to have an app controlling things from that perspective.

    Thursday, July 29, 2010 11:33 PM
  • Thanks Wissam,

    I would want to ask two things here
    1. Since I am providing password for connecting to publisher and subscriber and may be distributor.

    how can I really provide these values in .config file.

    Is there a way to have many variables  say strings declared in config file and then those variables can be used anywhere in project.
    If yes, can I really encrypt them. I dont mind performance cutdown this way.

    If our answer is yes Kindly give me some links to start up.

    2. Regarding Batch File.

    I actually converted batch file to exe using Bat_To_Exe_Convertor.
    It encrypts the exe file.
    I tried opening the file using hex reader but it shows some garbage value other than text.
    I am not sure how safe it is to still crack it some how.
    Note: workers just have execute writes over this exe file and they cannot copy it or nor they can email it. But not sure how that file still can be exploited.

    Kindly highlight on these issues.


    Friday, July 30, 2010 12:13 PM
  • saandii777:

    1. Yes, you can. to get you started: http://weblogs.asp.net/jgalloway/archive/2008/04/13/encrypting-passwords-in-a-net-app-config-file.aspx
    Performance is just bearly affected.

    2. I think you're OK with that too. Usually such kind of utilities simply converts to .exe and what you see with your hex editor is the compilation result and no encryption.

    It is safe to a good level but to answer you if it's still crackable, I say yes.

    Friday, July 30, 2010 1:23 PM
  • I would use RMO. Note that if you create a subscriber and then look at the job in sql server agent the passwords will be encrypted there. You might want to snag them from a subscriber with a sql server agent and then use these encrypted passwords everywhere.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Saturday, July 31, 2010 11:36 AM
  • If you are using RMO you can hardcode the passwords without having to use the app config files.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Saturday, July 31, 2010 11:36 AM
  • Hilary:

    If sandii777 hardcodes the passwords, he/she will have to obfuscate the code or else it can be easily decompiled and the passwords will be visible in plain text.

    Sunday, August 1, 2010 9:09 PM
  • Thank you.

    If you can please highlight How can I exactly snag the password from a subscriber with a sql server agent
    and use that encrypted password


    Monday, August 2, 2010 12:35 PM