Linked Server Problems

Question

Post reply

Linked Server Problems

brickpack

SSCrazy

Points: 2702
More actions
April 14, 2009 at 7:47 am

#133048

Hello,
I'm having some very frustrating issues getting my stored procs to run in SSRS. Here are two two different errors that I'm getting. The first error is when I try to execute a simple stored proc that queries a table on a linked server. The sproc works great in query analyzer, but when I try to create a report in SSRS it throws this error...
An error occurred while executing the query.
Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server. (Microsoft SQL Server, Error: 7415)
The error is generated when I try to use OPENQUERY() to yet another linked server...
An error occurred while executing the query.
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "BIDDY". (Microsoft SQL Server, Error: 7303)
These errors only happen in SSRS. When accessing the linked servers with ad-hoc queries from query analyzer, we see no strange behavior.
Please Help...

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Are the credentials used by SSRS the same as the ones you are using in SSMS?

Can you use OpenQuery in SSMS? The first error looks like Ad Hoc Distributed Queries are disabled.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

this worked perfect until before..never had any issues...thanks..

Bobby-676313 Mr or Mrs. 500 Points: 557 More actions · Answer 3

what is you connection string in ssrs ? you should always use your linked server host as datasource (server in which you configure linked server).

brickpack SSCrazy Points: 2702 More actions · Answer 4

Jack Corbett (4/14/2009)
Are the credentials used by SSRS the same as the ones you are using in SSMS?
Can you use OpenQuery in SSMS? The first error looks like Ad Hoc Distributed Queries are disabled.

Thanks for the reply Jack.

When I log into SSMS with the report user I get the same error. What kind of permissions does this user need in order to exec the sproc?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

I assume that you have granted the report user execute permissions on the procedure. Then you need to setup the Linked Server to either use a specific user on the remote server for all users OR you need to map the report user to a user on the remote server that has appropriate permissions on whatever objects you are accessing.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

brickpack SSCrazy Points: 2702 More actions · Answer 6

I think I figured out what the problem is, but I still don't know how to fix it.

The error happens when trying to access a postgres server from within our stored procedure...

DECLARE getpgdbs cursor FOR

SELECT distinct server,dbname FROM postgresDBs

OPEN getpgdbs

FETCH NEXT FROM getpgdbs INTO @server,@dbname

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT * FROM openrowset('MSDASQL', 'Driver={PostgreSQL ANSI};Database='+@dbname+';Server='+@server+'; .........

'SELECT value FROM config ;')

FETCH NEXT FROM getpgdbs INTO @server,@DBNAME

END

CLOSE getpgdbs

DEALLOCATE getpgdbs

In this example I'm using the driver to loop through hundreds of postgres databases and get my data.

Here's what I'm thinking is the permissions problem. The SQL user that's executing the sproc doesn't have system permissions on the physical server and can't access the postgres driver. Does that make sense to anyone? How can I remedy this? And...no I can't create 700+ system DSNs to connect to each postgres box.

Thanks

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 7

Dave

I use

SELECT * FROM openrowset('MSDASQL', 'Driver={PostgreSQL UNICODE};Database=xxxx;Server=yyyyy;

uid=xxxx;pwd=zzz','SELECT something FROM table')

in a stored procedure run to a remote Postgreserver. This works like a dream.

The login on The SQL-server 2005 is on a SQL-server account.

remember? uid and pwd

/Gosta

brickpack SSCrazy Points: 2702 More actions · Answer 8

Thanks for the reply Gosta. Getting the proc to run is not the problem. Getting the proc to run under a SQL account that has very limited access is where I'm having the issue.

I'm attempting to migrate our old procs into SSRS. The procs on the old server were all running under the 'sa' account and didn't have any problems at all. Our new server is locked down and I need to figure out how to run these procs with an account that doesn't have sysadmin rights. In fact, I only want the account to have read and exec.

So far...the only way that I can get the proc to work with the new account is to give it sysadmin rights. That defeats the purpose of the project as a whole.

What can I do?

Thanks