OPENDATASOURCE Question

Question

OPENDATASOURCE Question

Viewing 15 posts - 16 through 30 (of 38 total)

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

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

They also allow user name and password in clear text... a security risk, for sure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Glenn Owens SSCrazy Points: 2099 More actions · Answer 2

No - there is no firewall. All of the SQL Servers involved are within the private network/same domain.

Does anyone have any experience with using the opendatasource MS SQL function who might be able to shed some light on what these errors are and why the opendatasource statement works on some servers and not others?

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 3

A couple of other suggestions:

1. Can you please take a look at master..sysservers and check whether there are any entries there for the two servers that are behaving differently? Specifically, I would look to check if there are any differences in the communication settings options (RPC-IN, OUT, etc.).

The reason is- OPENDATASOURCE is RPC based. Linked-servers use additional communication features. It is possible that old config values in sysservers are making the central server treat the two remote servers differently.

2. You mentioned that the connection was killed immediately. Can you look at the prioritization (spelling?) for killing remote connections? [don't remember where the config is. I'll have to look it up and repost], Also, if you run sp_configure (with advanced options turned on) on both remote machines - can you please let us know whether there are any differences in the config values? (the run-values, I mean).

3. You are casting the SERVERNAME as VARCHAR(255) - is it possible that one remote server has a unicode or internationalized character in its name and the other does not?

4. Can you try to use the OPENDATASOURCE command from another server to the one that posted the connection issues? Does that work?

5. Are there any other differences between the two servers? e.g., is it possible that the problematic server is a named instance on a custom port and the one that works uses standard ports? Are there any such and other communication related differences?

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 4

No... wasn't talking about external security... it's an internal security risk that would fail a PCI audit.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 5

Or any other audit for that matter OPENDATASOURCE and all other OPEN commands use clear text passwords...

noeld SSC Guru Points: 96590 More actions · Answer 6

Can you confirm that you can "READ" *AND* not "WRITE" ?

* Noel

Glenn Owens SSCrazy Points: 2099 More actions · Answer 7

The SQL login being used for the opendatasoucre is secured to a single database, single table w/insert, update and select permissions on the table.

So... I've been able to select and update from several servers. The servers that fail the opendatasource fial for both SELECT and UPDATE.

bledu SSChampion Points: 12335 More actions · Answer 8

on your remote server can you check if the DisallowAdhocAccess registry option is explicitly set to 0

and the Ad Hoc Distributed Queries advanced configuration option is enabled.

Everything you can imagine is real.

Glenn Owens SSCrazy Points: 2099 More actions · Answer 9

I check the Disallowadhocaccess (see previous post). It is set to 0.

==========================

I've decided to abandon the use of opendatasource, opendataset, openquery....
There's simply too much variability and risk. I'm converting the code over to use dynamically created linked servers (hopfully this won't present a problem).

==========================

Thanks for all of the post and valueable input. As always... I learn a great deal from the expertise on this site.

Glenn

AlexSQLForums SSChampion Points: 14277 More actions · Answer 10

AlexSQLForums

SSChampion

Points: 14277

July 12, 2007 at 9:45 am

#719017

Linked server is the best option

Alex S

bledu SSChampion Points: 12335 More actions · Answer 11

i must have my eye checked

Everything you can imagine is real.

Glenn Owens SSCrazy Points: 2099 More actions · Answer 12

OK - under the category of: nothing is ever easy...

I'm trying to create a dynamic linked server from within the Stored Procedure that will be using the linked server.

Unfortunately it appears that I can't create the linked server in this way (I've even tried to break out the creation of the LKS into a separate stored procedure and makeing a nested call. The error message I get when I try to execute the creation of the "worker" stored procedure is:

Could not find server 'xyz' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

When I execute the same stored procedure (to create the LKS) as a simple query - everything work fine.

The only thing that I can think of is to add a separate job step to create the LKS.

Any suggestions???

Thanks

Glenn

Glenn Owens SSCrazy Points: 2099 More actions · Answer 13

DOH....

The answer to my rather stupid question is to use dynamic SQL which will delay the need for the linked server resource until runtime.

Thanks all!!!

Glenn

bledu SSChampion Points: 12335 More actions · Answer 14

Glenn,

It seems you need 'permanent' linked servers, why are you bothering with dynamic creation? I thought your requirement was to centrally drive stored procs!!!

Ta

Everything you can imagine is real.

Glenn Owens SSCrazy Points: 2099 More actions · Answer 15

I've got 40+ SQL Servers and I want to make the distribution of the the stored procedures encapsulated and portable - therefore the dynamic linkserver creation/drop.

The central natrue of the requirement is to provide read/write access to a central DB table from which the SPs will derive their parameters and be able to update runtime information.

Glenn