Post reply

Insert into linked server

  • February 9, 2017 at 8:24 am

    #321530

    Hi,
    One of my colleagues is trying to insert data into a table across a linked server, but the data's not making it into the table.  There is no error, and when the insert statement is executed, it says x rows updated on the messages tab.  However, the data is not in the table.

    A couple of things to note:
    - When I elevate his permissions to sysadmin, the insert across linked server works
    - He can select from the table no problem
    - When logged directly on to the remote server, he can insert into the same table directly

    The syntax being used is:

    INSERT INTO linkedserver.db.schema.table(dt, string)
    VALUES('2016-12-01', 'HELLO')

    Any ideas?

    Thanks,
    Andrew

  • February 9, 2017 at 8:55 am

    #1927560

    It sounds like not enough permissions on the target database.  What if you only give that account the db_datawriter role, does the insert succeed then?

  • February 9, 2017 at 9:17 am

    #1927567

    RVSC48 - Thursday, February 9, 2017 8:55 AM

    It sounds like not enough permissions on the target database.  What if you only give that account the db_datawriter role, does the insert succeed then?

    Hi,
    He has db_datareader and db_datawriter permissions on the target database already.  As mentioned, when logged in directly to the server, he can insert into the table fine using the same credentials.

    I should mention, the linked server is set to use the logins current security context

    Thanks
    Andrew

  • February 9, 2017 at 1:30 pm

    #1927647

    How is security set up on the linked server?

  • February 9, 2017 at 3:34 pm

    #1927663

    Joe Torre - Thursday, February 9, 2017 1:30 PM

    How is security set up on the linked server?

    Use the logins security context...

  • February 9, 2017 at 4:30 pm

    #1927667

    Using Windows Authentication sometimes if Kerberos isn't configured correctly SSPI contexts don't get passed.  This results in the inability to connect. If you're connected but the insert fails silently, that's weird.

  • February 9, 2017 at 9:09 pm

    #1927683

    adb2303 - Thursday, February 9, 2017 9:17 AM

    RVSC48 - Thursday, February 9, 2017 8:55 AM

    It sounds like not enough permissions on the target database.  What if you only give that account the db_datawriter role, does the insert succeed then?

    Hi,
    He has db_datareader and db_datawriter permissions on the target database already.  As mentioned, when logged in directly to the server, he can insert into the table fine using the same credentials.

    I should mention, the linked server is set to use the logins current security context

    Thanks
    Andrew

    As Joe Torre already mentioned above, it sounds like it could be the Kerberos "Security Feature".  Why it's not returning an error is odd but there is a "fix".  Write stored procedures for that fellow to use and, provided that the database owner is "sa" or some other sysadmin that has privs to the remote server, include EXECUTE AS OWNER in the stored procedure and then give that fellow the privs to execute (not change) that stored procedure.  Unless something really odd is going on, that should fix your problem.  And, no... I wouldn't change Kerberos... I DO consider it to be a "Security Feature".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 9, 2017 at 11:43 pm

    #1927693

    Thank you both for the suggestions about kerberos.  I'm pretty sure that side of things is working okay.  I can see kerberos connections coming from other servers, and this one, just fine.  When my colleague runs a SELECT statement against the table he's trying to INSERT into, I can see kerberos auth scheme being used via sys.dm_db_connections for his SPID.  Plus, there's none of those dreaded anonymous login errors you normally get with double hops (the insert isn't a double hop by the way - it's a direct connection).
    The fact that it fails silently is a pain, and a quandary.  Is there a way to force linked server errors back to the client?
    Could MSDTC be a culprit?
    Thanks

  • February 10, 2017 at 3:01 am

    #1927706

    I have changed to SQL to pull, rather than push the records.  That works fine.  Still interested to understand why the original INSERT fails...

    thanks

  • February 10, 2017 at 12:00 pm

    #1927862

    Sometimes you can pick up errors using Profiler. Some of the events in OLE DB and User Errors and Warning might give you more info - worth a try.

    Sue

  • February 10, 2017 at 2:40 pm

    #1927894

    Sue_H - Friday, February 10, 2017 12:00 PM

    Sometimes you can pick up errors using Profiler. Some of the events in OLE DB and User Errors and Warning might give you more info - worth a try.

    Sue

    I agree with Sue; I would try loading up SQL Profiler and see if anything is actually hitting the target server.

    It could be DTC (we had that problem once), and that is a pain to get working.

    The lesson we learned was to avoid linked servers where possible.  What we are currently doing instead is using service broker.  Not exactly the same beast, and results in data duplication with how we have it set up, but it gets us the same net result without risk of data loss presuming we have an emergency restart of a live SQL server instance.

    That being said, SQL instances with heavy traffic can get messy for service broker.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • February 10, 2017 at 3:34 pm

    #1927901

    adb2303 - Friday, February 10, 2017 3:01 AM

    I have changed to SQL to pull, rather than push the records.  That works fine.  Still interested to understand why the original INSERT fails...

    thanks

    That was going to be my next suggestion.  Still, the only way that I've been able to duplicate your problem is via the double hop.  If I login to the server directly, no problem because there's no double hop.

    I also don't get a silent failure... I do get a login failure.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 12 posts - 1 through 11 (of 11 total)

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