Joining tables with sql authentication
-
Hi,
I have a table called x on server y and database z
i can view the data on x only with sql authentication
i have a table called a on server b and database c
on b server i have windows authentication
now in a query analyzer (server b and database c)
i need to join table X (server y and database z which is with sql authentication)
with table a
Any Help is really Appreciated. Thanks much.
-
I hope this is a singleshot or investigative query you want to run, because the querytype proposed later on is certainly _not_ a general practise advise !!
connect to your server b
select ...
from X
inner join
(SELECT *
FROM OPENROWSET('SQLOLEDB','yourservera';'yourSQLusername';'supersecretpassword',
'select ... from yourdb_Z.owner.objectname where yourpredicate in ( ''v'', ''u'' ) ') ) RmtQry
on X.colx = RmtQry.colz
This kind of query is _not_alowed in any application at my company, except for DRP or investigative querying !
You could create a linked server and then query using the 4-part-name, that's more secure because the securitycontext is then placed in the linked server definition.
So this does not expose your userid and password in queries !Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
If you are going to be doing this kind of query, create a linked server and map the windows authenticated logins on one to a sql authenticated login on the other
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply