User permissions required for executing ad hoc OPENROWSET() query?

Question

Post reply

User permissions required for executing ad hoc OPENROWSET() query?

Rich Mechaber

SSChampion

Points: 11130
More actions
May 29, 2012 at 12:32 pm

#143494

I'm trying to execute an OPENROWSET() query as a logged in SQL user, without success. If I log in as the SQL user [ImaUser] and run this test query:
SELECT *
FROM OPENROWSET('SQLNCLI', 'server=myserver';'ImaUser';'TopSecretPass',
'SELECT * FROM DBA.dbo.LDAP') as a
the query expires as follows:
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
Considerations (both conducted from the same workstation):
1. The same query, executed as myself (a sysadmin) works fine, so it's not a server-wide configuration issue for ad hoc, remote queries.
2. If I log into SSMS as the user ImaUser and run just the SELECT query directly (SELECT * FROM DBA.dbo.LDAP), the query runs fine and returns rows.
I looked into database-level securables and permissions, but I didn't see anything there that helps. Likewise, BOL didn't indicate any special permissions were required for OPENROWSET() queries.
Thanks. I'm guessing this is something quick and obvious, but I'm not finding it,
Rich

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Looks like problem with connection string, try:

SELECT *

FROM OPENROWSET('SQLNCLI', 'Server=myserver;Database=DBA;UserId=ImaUser;Password=TopSecretPass',

'SELECT * FROM DBA.dbo.LDAP') as a

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Rich Mechaber SSChampion Points: 11130 More actions · Answer 2

Eugene Elutin (5/30/2012)
Looks like problem with connection string, try:
SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=myserver;Database=DBA;UserId=ImaUser;Password=TopSecretPass',
'SELECT * FROM DBA.dbo.LDAP') as a

Thanks Eugene, that didn't work either: I got this error:

Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists.

However, that got me some new error text to send to my friend Google, and I was able to get the following version of the OPENROWSET() query to work:

SELECT *

FROM OPENROWSET('SQLOLEDB', 'myserver';'ImaUser';'TopSecretPass',

'SELECT * FROM DBA.dbo.LDAP') as a

Not sure why the SQL native client failed but OLEDB worked; I'm not that familiar with the differences between them.

Thanks very much for your help,

Rich