SQL Linked Server and SQL Server Agent

When using SQL Server Agent to do a job that has a view or other that connects to a remote server then you may get the following issues:

Error “Linked servers cannot be used under impersonation without a mapping for the impersonated login”

Solution 1:

  1. Create a login on the end server as:
    USE [master]
    GO
    CREATE LOGIN [NT SERVICE\SQLSERVERAGENT] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[British]
    GO
  2. Then give this login the permissions on the objects required.
  3. Back on the host right-click on the remote server > Properties > Security >
  4. Add “Local server login to remote server login mappings:”
    Add “NT SERVICE\SQLSERVERAGENT” Impersonate = ticked on – No remote user or password
    Suggest use “Be made using the login’s current security context”

Solution 2:

  1. Create a new SQL server login on the end server and give it the required permissions
  2. Back on the host right-click on the remote server > Properties > Security >
  3. Add “Local server login to remote server login mappings:”
    Add “NT SERVICE\SQLSERVERAGENT” Impersonate = ticked off – Remote user = your new user, Password = new password
    Suggest use “Be made using the login’s current security context”

 

Advertisements

Windows 7 top tips

Sure there are many web pages discussing this. This is my quick run-down.

  1. Snipping Tool – Press Windows Key and type “snip ” until it is first option – or pin it to the task bar
  2. Drag window away from its full screen lock without using restore first
  3. Drag a window so that the cursor hits the left/right edge of screen at which point the window will snap left/right. Do this with two windows and you can compare two documents.
  4. Press the Windows Key and start typing to find documents, programs, control panel items. E.g. Windows Key > “Program and” and you  will get you “Programs and Features” and press return
  5. Sticky Notes – some people like them I don’t use them
  6. Number of Start Menu options. Right-click on task bar > Properties > Start Menu > Customize
    1. Use Large Icons – generally tick off
    2. Recent Items – generally tick on
    3. Show Video/Printers and many other options on/off
  7. Use Pin to Start Menu / Task Bar
  8. Put Office Programs on the Task Bar and then use Jump Lists by right-clicking them
    1. Pin your most common items to jump lists
    2. Note Jump Lists do very occasionally get forgetful. There is a sophisticated way of backing these up, beyond most normal working
  9. Drag and drop items around the Task Bar

 

If you find others then please comment and I can add them to the main article here.

Or links to pages that list these out.