RE: Use a Trusted Connection With OPENROWSET?
From: Mike Epprecht (SQL MVP) (mike_at_epprecht.net)
Date: 09/21/04
- Next message: Mary Bray: "Re: Best practices for remote users"
- Previous message: Mike Epprecht (SQL MVP): "RE: Problem with ASP & SQL ODBC connection"
- In reply to: John: "Use a Trusted Connection With OPENROWSET?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 21 Sep 2004 01:25:03 -0700
Hi
The OPENROWSET is done in the context of the SQL Server account that is
running on the server. Most of the time it is the local machine account. Make
sure SQL Server is running under a doamin account and that that account has
rights on the destination server.
Regards
Mike
"John" wrote:
> Has anybody, anywhere figured out a way to use a trusted connection (Windows
> authentication) with OPENROWSET? The first two queries below used to work
> just fine because they use SQL authentication. The four queries after that
> are various sad, sad (and unworking) attempts I've made at using Windows
> authentication.
>
> We've begun cycling the sa password daily so the first two queries no longer
> work. There are ways to deal with that, so the problem I'm left with is
> merely an academic one. Can a trusted connection be used with OPENROWSET?
> If so, what would the query look like?
>
>
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB', 'myserver'; 'sa'; 'mypassword', 'SET FMTONLY OFF
> EXEC master..sp_who2') AS a
> WHERE a.BlkBy <> ' . '
> ORDER BY a.ProgramName
>
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB', 'myserver'; 'sa'; 'mypassword', 'SET FMTONLY OFF
> EXEC master..sp_who2') AS a
> WHERE a.BlkBy <> ' . '
> ORDER BY a.ProgramName
>
> ----------------------------------------------------------------------
>
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB', 'DRIVER={SQL Server}; SERVER=myserver;Trusted
> Connection=yes', 'SET FMTONLY OFF EXEC master..sp_who2') AS a
> WHERE a.BlkBy <> ' . '
> ORDER BY a.ProgramName
>
> SELECT a.*
> FROM OPENROWSET('Driver={SQL
> Server};Server=myserver;Database=master;Trusted_Connection=yes, ''''SET
> FMTONLY OFF EXEC master..sp_who2') AS a
> WHERE a.BlkBy <> ' . '
> ORDER BY a.ProgramName
>
> SELECT a.*
> FROM OPENROWSET('Provider=sqloledb;Data Source=myserver;Initial
> Catalog=pubs;Integrated Security=SSPI, ''''SET FMTONLY OFF EXEC
> master..sp_who2''') AS a
> WHERE a.BlkBy <> ' . '
> ORDER BY a.ProgramName
>
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB', 'myserver'; 'Integrated Security=SSPI ', 'SET
> FMTONLY OFF EXEC master..sp_who2') AS a
> WHERE a.BlkBy <> ' . '
> ORDER BY a.ProgramName
>
>
>
- Next message: Mary Bray: "Re: Best practices for remote users"
- Previous message: Mike Epprecht (SQL MVP): "RE: Problem with ASP & SQL ODBC connection"
- In reply to: John: "Use a Trusted Connection With OPENROWSET?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
