This worked for us:
select B.* from openrowset('MSDASQL', 'Driver={SQL SERVER};Server=Server2;UID=User2; PWD=******', 'select * from Branch') as B
But we don't want to specify the password in our code. However, the UID/Pwd are the same on Server2. Is there a way to do this? But we also don't want to use Linked Server.
I can see why you don't want to hard-code the pwd, but what's the problem with using a linked server? They're kinda made for this sort of thing.
A totally different approach would be to use a SSIS package to do it. If you're using a Windows login that exists and has permissions on both servers, you can connect with integrated security. if you're not using a Windows login, why not?
Same thing for your original query, I believe (untested)
Sorry, I don't have good answers to your questions. This involves a lot of others that have made decisions, in other countries and other departments and I don't know the reasons for everything.
I think what I'm trying to do is pass-through. i.e. pass my current credentials whatever they may be, to another server.
For pass-through, you will need to use windows authentication (Integrated security). Have you tried that?
Thankyou. But the app uses SQL Authentication.
I tried it uses Windows Authentication anyways. I get:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
OK, then your choices are more limited. You probably have to stick with SQL auth. which means openrowset or linked server, I think
1 Like
If you can use SSIS that would be the better way - you can store the password securely in Integration Services Catalog. If that is not an option - then you should consider dynamic SQL for this where you store the username/password in a table (encrypted - of course) and build the parameters for openrowset.
Just be aware that using openrowet may send the password in clear text and could be captured in a profiler trace.
1 Like