OPENROWSET with Windows Authentication

• daleintacoma

  Ten Centuries

  Points: 1224

  More actions

  October 2, 2012 at 5:28 pm

  #282505

  I'm trying to get a simple OPENROWSET to work across two SQL 2008 R2 machines using a domain login.

  Here's what I've got:

  SELECT a.*

  FROM OPENROWSET('SQLOLEDB',

  'SERVER=myserver;DATABASE=mydatabase;Trusted_Connection=Yes;Integrated Security=SSPI;User Id=mydomain\myuser;Password=blahblahblah;',

  'SELECT @@SERVERNAME') AS a

  And I'm getting the error:

  Msg 18456, Level 14, State 1, Line 1

  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

  OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Invalid connection string attribute".

  I've spent hours trying to find a functioning example, including here on sql central. I can't figure out what the correct connection attributes are. Can anyone provide a known working example?

  The eventual query will execute only a few times a year. Thus, I don't want to add a linked server. Windows Auth is required.

  Thanks in advance. --Dale