none
Dynamic SQL, Linked Servers, CLR, oh my! RRS feed

  • Question

  • Here's the gist of what I'm doing.  I'm generating a dynamic SQL statement that executes a CLR stored procedure which accesses a linked server. (whew!) 

    Here’s a sample.  This is within a stored procedure:

    DECLARE @remote_server sysname = N'MyRemoteServer',
            @remote_db sysname = N'DBonRemoteServer',
            @sql nvarchar(MAX);
     
            SET @sql = N'EXEC SQL#.DB_BulkCopy
                @SourceType = N''MSSQL'',
                @SourceConnection = NULL,
                @SourceQuery = N''SELECT ASOF_YYYYMM FROM dbo.ref_gl_tmp'',
                @DestinationConnection = N''Data Source=' + @remote_server + N';Integrated Security=true;Initial Catalog=' + @remote_db + N''',
                @DestinationTableName = N''base.REF_GL'',
                @BatchSize = 10000,
                @NotifyAfterRows = 0,
                @TimeOut = 10000,
                @ColumnMappings = N''ASOF_YYYYMM,ASOF_YYYYMM'',
                @BulkCopyOptionsList = N''KeepIdentity|CheckConstraints|TableLock|KeepNulls'',
                @RowsCopied = -1;
                ';
     
                EXEC sp_executesql @sql;

    ** The columns and table names above are just test values.  I don't believe they're relevant to the question.

    The MyRemoteServer parameter is a linked server to a different SQL Server.

    The linked server has myself and the SQL Server domain service account mapped to a SQL login on the remote server.

    When I execute the stored procedure that has this code, it errs out with the message that the local SQL Server domain service account doesn’t have permissions on the remote SQL Server.

    When I grant permissions to that domain account to the remote SQL Server, it works.

    I have a few ideas of what’s going on, but for some reason I’m having a brain fart as to why it’s not utilizing the linked server mapping and connecting to the linked server via the SQL login.


    Friday, March 3, 2017 8:07 PM

Answers

  • It looks like this "DB_BulkCopy" is a CLR proc that connects to the remote server using .NET SqlClient.

    If so then it would not use the linked server definition at all; it's making an entirely separate connection.

    If you want to load data into a local table using a linked server, you don't need all this machinery.

    Just run

    insert into mylocaltable select ... from mylinkedserver.database.dbo.sometable\

    or

    insert into mylocaltable select ... from openquery(mylinkedserver, N'SELECT ASOF_YYYYMM FROM dbo.ref_gl_tmp')

    David


    Microsoft Technology Center - Dallas
    My blog

    • Marked as answer by WellOfDavid Friday, March 3, 2017 11:45 PM
    Friday, March 3, 2017 8:29 PM
  • The value of @remote_server is being concatenated into a Connection String that defines how the client software (in this case, .NET SqlClient) should connect to the remote instance. So, as David mentioned, there is no concept of Linked Servers here.

    The reason for the error related to the service account for the local instance is that SQLCLR, by default, uses the security context of the service account (much like xp_cmdshell when a proxy account is not involved). SQLCLR does have the option of using Impersonation which will instead use the security context of the Windows Login executing the code (SQL Server Logins cannot use Impersonation as they are not tied to a Windows SID), but that is not being employed here (though I can add that ability in the future :-).

    For now your two options are:

    1. Use Integrated Security (a.k.a. Trusted_Connection) which requires giving access to the remote server to the local Instance's service account, or
    2. Do NOT use Integrated Security and instead set up a SQL Server Login on the remote server and specify its "User ID" and "Password" in the Connection String.

    Take care,

    Solomon...

    • Marked as answer by WellOfDavid Friday, March 3, 2017 10:05 PM
    Friday, March 3, 2017 9:52 PM

All replies

  • It looks like this "DB_BulkCopy" is a CLR proc that connects to the remote server using .NET SqlClient.

    If so then it would not use the linked server definition at all; it's making an entirely separate connection.

    If you want to load data into a local table using a linked server, you don't need all this machinery.

    Just run

    insert into mylocaltable select ... from mylinkedserver.database.dbo.sometable\

    or

    insert into mylocaltable select ... from openquery(mylinkedserver, N'SELECT ASOF_YYYYMM FROM dbo.ref_gl_tmp')

    David


    Microsoft Technology Center - Dallas
    My blog

    • Marked as answer by WellOfDavid Friday, March 3, 2017 11:45 PM
    Friday, March 3, 2017 8:29 PM
  • The value of @remote_server is being concatenated into a Connection String that defines how the client software (in this case, .NET SqlClient) should connect to the remote instance. So, as David mentioned, there is no concept of Linked Servers here.

    The reason for the error related to the service account for the local instance is that SQLCLR, by default, uses the security context of the service account (much like xp_cmdshell when a proxy account is not involved). SQLCLR does have the option of using Impersonation which will instead use the security context of the Windows Login executing the code (SQL Server Logins cannot use Impersonation as they are not tied to a Windows SID), but that is not being employed here (though I can add that ability in the future :-).

    For now your two options are:

    1. Use Integrated Security (a.k.a. Trusted_Connection) which requires giving access to the remote server to the local Instance's service account, or
    2. Do NOT use Integrated Security and instead set up a SQL Server Login on the remote server and specify its "User ID" and "Password" in the Connection String.

    Take care,

    Solomon...

    • Marked as answer by WellOfDavid Friday, March 3, 2017 10:05 PM
    Friday, March 3, 2017 9:52 PM
  • Ahhhhhhh!!!!  Well, that explains that!  I was definitely confuzzled there originally.  Thank you Solomon for the clear explanation!

    And thank you also David.  Your explanation was actually the answer too, although, I didn't quite connect the dots completely...sorry.

    Friday, March 3, 2017 10:07 PM
  • DECLARE @remote_server sysname = N'MyRemoteServer',
            @remote_db sysname = N'DBonRemoteServer',
            @sql nvarchar(MAX);

            SET @sql = N'EXEC SQL#.DB_BulkCopy
                @SourceType = N''MSSQL'',
                @SourceConnection = NULL,
                @SourceQuery = N''SELECT ASOF_YYYYMM FROM dbo.ref_gl_tmp'',
                @DestinationConnection = N''Data Source=' + @remote_server + N';Integrated Security=true;Initial Catalog=' + @remote_db + N''',
                @DestinationTableName = N''base.REF_GL'',
                @BatchSize = 10000,
                @NotifyAfterRows = 0,
                @TimeOut = 10000,
                @ColumnMappings = N''ASOF_YYYYMM,ASOF_YYYYMM'',
                @BulkCopyOptionsList = N''KeepIdentity|CheckConstraints|TableLock|KeepNulls'',
                @RowsCopied = -1;
                ';

                EXEC sp_executesql @sql;

    This has nothing to with the original question, but I like to point out there is no reason to use dynamic SQL for the procedure call. You will need a variable to hold the value for the parameter @DestinationConnection.

    So what is the problem with the dynamic SQL here, you may ask. Well, it makes the code more difficult to read and maintain.

    Saturday, March 4, 2017 3:22 PM