locked
Sql server job is failing after upgrade RRS feed

  • Question

  • Hello,

    We upgraded our database server from windows server 2008 r2 sp1 to windows server 2012 r2. Our some jobs are failing. It seems like we are having some configuration issues. I am also attaching my history log. Do you ever get this kind of error before?

    I am not getting any error in stored procedure which that SQL Server job belongs. But I am getting an error when I executed this SQL Server job.

    Job 'Create_Web_Access_Account_Until_Powershell_Activated' : Step 1, 'create_web_access_code' : Began Executing 2016-08-15 13:35:49

    Msg 7416, Sev 16, State 1, Line 655 : Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000]
    Msg 0, Sev 16, State 1, Line 91 : Begin Candidates Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 141 : Begin Students Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 228 : Begin Faculty Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 284 : Begin Advisors Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 320 : Begin Advisees Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 361 : Begin Alumni Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 397 : Begin Staff Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 634 : Begin Set Web Access Codes AlternateAug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 651 : CUS_ManageActiveDirectory with latest Active Directory Aug 15 2016  1:35PM [SQLSTATE 01000]

    Thanks


    Virtual Reality

    Monday, August 15, 2016 8:37 PM

Answers

  • what store procedure code ?In code there will linked server.Check  using following command.

    Cause: When creating a linked server with the parameter @provstr and you use a local SQL Server non-admin or non-Windows account, you have to add the parameter “User Name”   into the @provstr

    Resolution : Add “User ID=Username” into the provider string on your linked server

    EXEC master.dbo.sp_addlinkedserver @server = N’LinkServerName’, @provider=N’SQLNCLI’,@srvproduct = ‘MS SQL Server’, @provstr=N’SERVER=serverName\InstanceName;User ID=myUser‘ 

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’LinkServerName’, @locallogin = NULL , @useself = N’False’, @rmtuser = N’myUser’, @rmtpassword = N’*****’

    Check:

    SELECT  TOP 1 * FROM LinkServerName.msdb.dbo.backupset
    GO
    SELECT * FROM OPENQUERY (LinkServerName, ‘SELECT TOP 1 * FROM msdb.dbo.backupset ‘)

    Refer following link

    https://blogs.technet.microsoft.com/mdegre/2011/03/10/access-to-the-remote-server-is-denied-because-no-login-mapping-exists/


    Please click Mark As Answer if my post helped.

    Monday, August 15, 2016 8:54 PM
  • Hello,

    We upgraded our database server from windows server 2008 r2 sp1 to windows server 2012 r2. Our some jobs are failing. It seems like we are having some configuration issues. I am also attaching my history log. Do you ever get this kind of error before?

    I am not getting any error in stored procedure which that SQL Server job belongs. But I am getting an error when I executed this SQL Server job.

    Job 'Create_Web_Access_Account_Until_Powershell_Activated' : Step 1, 'create_web_access_code' : Began Executing 2016-08-15 13:35:49

    Msg 7416, Sev 16, State 1, Line 655 : Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000]
    Msg 0, Sev 16, State 1, Line 91 : Begin Candidates Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 141 : Begin Students Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 228 : Begin Faculty Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 284 : Begin Advisors Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 320 : Begin Advisees Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 361 : Begin Alumni Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 397 : Begin Staff Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 634 : Begin Set Web Access Codes AlternateAug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 651 : CUS_ManageActiveDirectory with latest Active Directory Aug 15 2016  1:35PM [SQLSTATE 01000]

    Thanks


    Virtual Reality

    Good day,

    In first glance it seems like simple issue no login-mapping exists .

    1. You should remember that by creating logins/users in the new server you do not get the same element as in the old server. Check issue related to "orphaned users" (search in google for more information) for example.

    2. Back to the question Access to the remote server is denied because no login-mapping exists.

    Unfortunately, you did not post your code and I cannot read minds. I can point a common cases:

    >> Using linked server with the parameter @provstr, which use a local SQL Server non-admin or non-Windows account

    In this case add to the provider connection the user mapping: "User ID=MyUser"

    EXEC master.dbo.sp_addlinkedserver @server = N'ServerName', @provider=N'SQLNCLI',@srvproduct = 'MS SQL Server', @provstr=N'SERVER=serverName\InstanceName;User ID=MyUser'



    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, August 15, 2016 9:04 PM

All replies

  • what store procedure code ?In code there will linked server.Check  using following command.

    Cause: When creating a linked server with the parameter @provstr and you use a local SQL Server non-admin or non-Windows account, you have to add the parameter “User Name”   into the @provstr

    Resolution : Add “User ID=Username” into the provider string on your linked server

    EXEC master.dbo.sp_addlinkedserver @server = N’LinkServerName’, @provider=N’SQLNCLI’,@srvproduct = ‘MS SQL Server’, @provstr=N’SERVER=serverName\InstanceName;User ID=myUser‘ 

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’LinkServerName’, @locallogin = NULL , @useself = N’False’, @rmtuser = N’myUser’, @rmtpassword = N’*****’

    Check:

    SELECT  TOP 1 * FROM LinkServerName.msdb.dbo.backupset
    GO
    SELECT * FROM OPENQUERY (LinkServerName, ‘SELECT TOP 1 * FROM msdb.dbo.backupset ‘)

    Refer following link

    https://blogs.technet.microsoft.com/mdegre/2011/03/10/access-to-the-remote-server-is-denied-because-no-login-mapping-exists/


    Please click Mark As Answer if my post helped.

    Monday, August 15, 2016 8:54 PM
  • Hello,

    We upgraded our database server from windows server 2008 r2 sp1 to windows server 2012 r2. Our some jobs are failing. It seems like we are having some configuration issues. I am also attaching my history log. Do you ever get this kind of error before?

    I am not getting any error in stored procedure which that SQL Server job belongs. But I am getting an error when I executed this SQL Server job.

    Job 'Create_Web_Access_Account_Until_Powershell_Activated' : Step 1, 'create_web_access_code' : Began Executing 2016-08-15 13:35:49

    Msg 7416, Sev 16, State 1, Line 655 : Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000]
    Msg 0, Sev 16, State 1, Line 91 : Begin Candidates Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 141 : Begin Students Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 228 : Begin Faculty Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 284 : Begin Advisors Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 320 : Begin Advisees Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 361 : Begin Alumni Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 397 : Begin Staff Aug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 634 : Begin Set Web Access Codes AlternateAug 15 2016  1:35PM [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 651 : CUS_ManageActiveDirectory with latest Active Directory Aug 15 2016  1:35PM [SQLSTATE 01000]

    Thanks


    Virtual Reality

    Good day,

    In first glance it seems like simple issue no login-mapping exists .

    1. You should remember that by creating logins/users in the new server you do not get the same element as in the old server. Check issue related to "orphaned users" (search in google for more information) for example.

    2. Back to the question Access to the remote server is denied because no login-mapping exists.

    Unfortunately, you did not post your code and I cannot read minds. I can point a common cases:

    >> Using linked server with the parameter @provstr, which use a local SQL Server non-admin or non-Windows account

    In this case add to the provider connection the user mapping: "User ID=MyUser"

    EXEC master.dbo.sp_addlinkedserver @server = N'ServerName', @provider=N'SQLNCLI',@srvproduct = 'MS SQL Server', @provstr=N'SERVER=serverName\InstanceName;User ID=MyUser'



    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, August 15, 2016 9:04 PM