ad-hoc query using OPENROWSET | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

ad-hoc query using OPENROWSET

Hi there,<br /><br />How can I perform ad-hoc query with OPENROSET using trusted connection ?<br /><br />This rowset function gets a parameter that includes the data source(server name), user id and password. I thought that the parameter "provider_string" will probably be useful in this case but it is not. <br /><br />I used the follwing provider_string, taken from an OLEDB connection to the local SQL Server (database PerfAnalysis) that I have set using trusted connection (I have taken the text of the UDL file ):<br /><br />Provider=SQLOLEDB.1;Integrated Security=SSPI<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ersist Security Info=False;Initial Catalog=PerfAnalysis;Data Source=LocalSQLServer<br /><br />Any suggestion ?<br /><br />Thanks.<br /><br />Aviel Iluz<br />Database Administrator<br />Pacific Micromarketing<br />Melbourne Australia<br /><br />
I think that the server is configured for trusted connections only, use the following :
select * from
‘Trusted_Connection=yes;Integrated Security=SSPI;Data Source=(local);’,
‘set fmtonly on exec mystoredproc 1’
);EN-US;296711 for reference. HTH Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks you very much, Satya. It works !
Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia