Post reply

Mirror Linked Server needs sysadmin role

  • May 5, 2010 at 12:14 am

    #220409

    I have a database mirrored on ServerA and ServerB. On ServerC I want to create a linked server to that database.

    This I can do, but the mapped logings only function if the ServerC user has sysadmin on ServerC. If I add sysadmin role to the user it works - and if I remove it it fails with the error "Msg 7416, Level 16, State 2, Line 1

    Access to the remote server is denied because no login-mapping exists." The only difference is the sysadmin role.

    I don't want to grant all the users sysadmin on the server, so any help would be appreciated. An interesting is that it seems to revolve around whether @provstr is specified (required for mirrored linked servers) as even a non-mirrored database with that parameter specified exhibits the same problem (though doesn't need this parameter.)

    Code:

    -- Linked Server definition

    EXEC master.dbo.sp_addlinkedserver

    @server = N'TheMirroredDatabase'

    , @srvproduct=''

    , @provider=N'SQLNCLI'

    , @provstr=N'server=ServerA;failoverpartner=ServerB;network=dbmssocn;database=MirroredDatabase;'

    -- User Security Context

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TheMirroredDatabase',@useself=N'False',@locallogin=N'TheUser',@rmtuser=N'TheRemoteUser',@rmtpassword='T0pS3cr3t'

    -- Generic Security Context

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TheMirroredDatabase', @locallogin = NULL , @useself = N'False', @rmtuser = N'TheRemoteUser', @rmtpassword = N'T0pS3cr3t'

    Thanks,

    Steve.

  • May 12, 2010 at 12:20 am

    #1165971

    In case in the distant future archaeologists dig up this query and wonder as to the solution, this is what I found.

    The @provstr also requires the UID and PWD elements. However, there are two things I would note:

    1) using the UID and PWD elements was something obvious I tried previously and received the same error, so there is perhaps another quirk which affects this. I did consider that I'd passed an invalid UID/PWD but...

    2) the UID/PWD don't need valid values as they are never used except if you have specified the linked server security "Be made without using a security context" and the user account is not defined otherwise.

    Ultimately the simplest string needed to be:

    @provstr=N'server=<principal>;failover partner=<mirror>;database=<database>;uid=;pwd=;'

    Steve.

Viewing 2 posts - 1 through 1 (of 1 total)

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