Post reply

Access to a secondary log shipped database but not to the primary.

  • July 24, 2008 at 12:49 am

    #381882

    I have a requirement to provide sql access, not windows, to a customer to the secondary log shipped database, but cannot let them have access to the primary database. The secondary database is a type of "reporting" server to them, where they connect remotely over port 1433 and can run read only queries to extract data etc.

    In SQL 2000, I did this by using different logins but using the same sid acros database server's, this doesn't appear work in SQL 2005.

    Any info would be appreciated,

    Thanks, Col

  • July 24, 2008 at 7:41 am

    #847743

    Would a DDL Trigger work for you? The DDL Trigger would determine if the server name is A or B, and allow or deny the connection for a specific login based on this information.

    Here is an example of a logon trigger that checks against a list of servers and logins.

    http://blogs.technet.com/vipulshah/archive/2007/12/04/ddl-triggers-and-logon-triggers.aspx

    I haven't played with Log Shipping so I do not know if this would work - please let me know.

    Herve Roggero
    hroggero@pynlogic.com
    MCDBA, MCSE, MCSD
    SQL Server Database Proxy/Firewall and Auditing

  • July 24, 2008 at 11:39 pm

    #848214

    Thanks Herve, I'll take a look at the article.

  • July 27, 2008 at 10:07 pm

    #848949

    I've discovered that this can be actually acheived in SQL 2005 similar to the way , the steps are pretty simple...

    On the Primary Server

    1. Create a Login on the primary server that will not be disclosed to the client Login_Not_Disclosed_To_Client

    2. Create a User in the source database on the source server User_Disclosed_To_Client and assign appropriate role.

    On the Secondary Server

    1. Determine SID value for the User User_Disclosed_To_Client i.e. select SID from .sys.sysusers where name = 'User_Disclosed_To_Client'

    2. Create a Login on the secondary server that will be disclosed to the client using the SID obtained in the previous step Login_Disclosed_To_Client i.e. CREATE LOGIN Login_Disclosed_To_Client WITH PASSWORD = 'password_disclosed_to_Client', SID =0xAB0847966CD2CF47AD1E0A6FCC521C42, CHECK_POLICY = OFF

    And your done, the client now has access to the secondary server, but unless they obtain the login credentials to the primary server, they won't be able to access it.

    Cheers, Col

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply