Select from another server using same credentials

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