dcsimg
Results 1 to 6 of 6

Thread: [RE-RESOLVED] SQL Server 2012 to 2016

  1. #1

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,881

    Resolved [RE-RESOLVED] SQL Server 2012 to 2016

    I have a stored procedure running on SQL Server 2012. The SP queries data from a different SQL Server in a different location. Just recently, that other SQL Server was transitioned to 2016, at which point the stored procedure has started failing. I'm not sure that the versions are the reason, but it sure seems likely.

    I then tried simply creating a view in a DB in the 2012 instance that was just SELECT * FROM <a view in the 2016 instance>. This failed in the same way as the stored procedure failed. The error message is:

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

    I've never dealt with interacting with newer versions of SQL Server before. SSMS is now so distinct that it makes sense that I can use it to look at 2012, 2014, 2016, and likely others, but I'm trying to figure a way to access the 2016 from the 2012. I suppose the other alternative would be to have a stored procedure on the 2016 that populated a table on the 2012 on demand, though that seems kind of hokey. Without any experience in this area, I'm looking for suggestions.
    Last edited by Shaggy Hiker; Jul 12th, 2019 at 11:23 AM.
    My usual boring signature: Nothing

  2. #2
    Junior Member
    Join Date
    Jun 2019
    Posts
    26

    Re: SQL Server 2012 to 2016

    This seems similar to environment we are using on setup with three remote locations and servers are linked to each other. Two SQL servers are 2008R2, one is 2012 and everything works, but we haven't tested with 2016. But the error shows the problem is related to the connection security.

    Can you check if you are seeing in the Management Studio the other linked server? You can browse the databases on the server with the supplied credentials (if any, not anonymous)?

    If you get an error, you can check the properties of the linked server and set the proper credentials:



    Quick test and getting the screenshot from SQL Server 2008R2 (running on XP) linked to SQL Server 2017 (running on Win10). At first, adding the linked server in SSMS shown me an error that authentication failed, but I've changed it according the screenshot above (with the proper user name/password) and now I can browse the remote databases, query them and do whatever I want (the credentials I've used give me full access to the remote).

  3. #3

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,881

    Re: SQL Server 2012 to 2016

    I was about to post back, and since you showed the relevant screenshot, I won't bother. That screen is also present in SQL Server 2012, and appears to be the key.

    We had a different radio button checked. None of us have any memory of changing that on the link to the old server, but whatever, that was years ago. We did have to have the security context set, as you have shown. That appears to have solved the issue...almost entirely. I'm running into an issue, still, but with the execution of the stored procedure, and that hasn't been tracked down, yet. It is likely unrelated.
    My usual boring signature: Nothing

  4. #4
    Junior Member
    Join Date
    Jun 2019
    Posts
    26

    Re: [RESOLVED] SQL Server 2012 to 2016

    It could be again access rights issue?

    You can try testing part by part of the stored procedure by executing few lines each time (of course keep the correct parameters or just hardcode them in the query) to see where is the exact SQL code that causes the problem and add more info so here someone can check?

  5. #5

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,881

    Re: [UN-RESOLVED] SQL Server 2012 to 2016

    I thought this was resolved...and then it came back again this morning, just in a different fashion.

    There are a couple issues. One is a total mystery, but doesn't need to be solved. The other is a puzzle, at this time, and does need to be solved.

    The total mystery is that there is a login on the second server that I simply cannot use to login with. We've changed the password, typed user name and password, copy and pasted user name and password, and done each multiple times (only changed the password one time), but I simply cannot login using that account. I can with other accounts, but not that one. We have no idea why not, but it doesn't really matter, as that account isn't necessary.

    The puzzle that I will be trying to study in a little bit is that I can run the stored procedure on the first server and it works fine, but when a service runs the stored procedure on the first server, it fails. The stored procedure runs a second stored procedure which joins on data located on the second server. It's that join that is failing, but I don't have a useful error message, yet, so there's not much more to say about it. When I run the stored procedure, all is well. When the service calls the stored procedure, it fails. This is almost certainly a permissions issue related to this thread, and I hope to have better information later on today.
    My usual boring signature: Nothing

  6. #6

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,881

    Re: [RE-RESOLVED] SQL Server 2012 to 2016

    Still don't know what's going on with the total mystery question, but I barely care, too. It's curious, but intractable.

    Solved the other one by changing permissions on the page shown in post #2 (very useful, that screenshot). It's a bit interesting that what we had to do was different from what we had done on the old server, but neither of us really remembers setting up the old server anyways, so who knows.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width