March 27, 2008 at 8:41 am
Greetings Gurus!
We are in the process of moving databases from SQL 2000 to 2005 and I have a SQL Server Agent job that runs nightly. When I run this on the 2005 box, I get this error:
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
Here is the statement I'm running...
Select ACNETLOGON, EMPLID, FIRSTNAME, LASTNAME, NAME, 'No', TITLE, DEPTID, DEPARTMENT, LOCATION,REG_REGION
from OPENDATASOURCE ('SQLOLEDB', 'Data Source=(server name deleted);User ID=Timecrd3;Password=TIMEATTEND')
.(db name deleted).dbo.ACViewGlobalDB
Where EMPLID not in (Select Distinct EMPLID from tblUser where EMPLID IS NOT NULL)
and ACNETLOGON IS NOT NULL
AND ACNETLOGON <> ''
I know how to access data from a different server via Linked Servers, but I figured that now, when moving the db, is the time to find out the "best practice" way of doing this.
All this statement does is update a user table from our HR system.
I'm tending to think that the way I'm doing it is not good because it has the password in clear text (but this user does not have access to any sensitive data.)
Any guidance would be greatly appreciated.
Bob
March 27, 2008 at 9:08 am
Apparently MS thinks using linked servers is more secure than opendatasource as they have it disabled by default.:D
Personally, I prefer using linked servers. The only caveat is that I have found that, if I have access, creating a stored procedure on the linked server and calling that has exponentially improved performance. Mainly because all processing is done on the linked server so less is coming across the network.
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
March 27, 2008 at 2:11 pm
I agree with Jack.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply