Post reply

EXECUTE AS Problem

  • July 10, 2012 at 7:54 am

    #260914

    I have a stored procedure that extracts data from a database on another server using a linked server. The stored proc is as follows:

    WITH EXECUTE AS 'MyLogin'

    AS

    EXEC dbo.MyExtract

    When I run the stored proc I get the following errors:

    OLE DB provider "SQLNCLI10" for linked server "XXXX" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI10" for linked server "XXXX" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

    Msg 65535, Level 16, State 1, Line 0

    SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF].

    However if I comment out the "WITH EXECUTE AS" it works correctly.

    1. I can log on to the source database server from my client machine and extract data from the tables

    2. I can log on to my server (which is not the source database server) and extract data from the source database tables

    3. The user id I am logging on with is a Windows user account; SPNs have been setup and have been tested i.e. step 1 above

    Thanks

  • July 10, 2012 at 8:42 am

    #1510984

    just checking the basics here:

    MyLogin is actually a user in the database, right? you know it cannot be a login, but must be a user, right?

    --the EXECUTE AS must be a user in the database...not a login

    CREATE procedure pr_CallBoostedSecurityProcess

    WITH EXECUTE AS 'superman'

    AS

    BEGIN

    dbcc freeproccache

    END

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 10, 2012 at 9:06 am

    #1511008

    Yes, it is a Windows user account not a SQL login and the account exists in both Server A and Server B (the source database server).

  • July 10, 2012 at 9:11 am

    #1511014

    I would test the linked server as that user to see if the problem is in the credentials:

    EXECUTE AS USER='mydomain\lowell'

    --who am i?

    select user_name()

    ,suser_name()

    --do stuff

    SELECT TOP 1 * FROM myLinkedServer.DatabaseName.dbo.TableName

    --change back into superman

    REVERT;

    if that fails, it's just a tweak on the linked server.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 11, 2012 at 1:56 am

    #1511375

    Yes, when I run your code snippet I get the same error message as per my original post.

  • July 11, 2012 at 8:04 am

    #1511560

    Can you successfully query the linked server using any login?

    Can you post your linked server configuration?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 6 posts - 1 through 5 (of 5 total)

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