Encrypted Password

Question

Encrypted Password

SATYA R

Right there with Babe

Points: 715
More actions
October 24, 2018 at 12:29 am

#409140

Hi Guru's,
I have weird situation while passing username and password in BCP command. Can any one let me know how to pass encrypted password in BCP?
Note: We don't have Trusted connection option (-T).
Any inputs?

Viewing 13 posts - 1 through 12 (of 12 total)

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

sgmunson SSC Guru Points: 110639 More actions · Answer 1

SATYA R - Wednesday, October 24, 2018 12:29 AM
Hi Guru's,
I have weird situation while passing username and password in BCP command. Can any one let me know how to pass encrypted password in BCP?
Note: We don't have Trusted connection option (-T).
Any inputs?

You say it's "weird", but don't explain what the weird is ? That's not gonna fly in this forum. If you would need to encrypt your password, it would probably have to be done ahead of time in some fashion, and you'd thus need to know how. So, first step.... why is that needed, and what encryption method is it?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

SATYA R Right there with Babe Points: 715 More actions · Answer 2

Hi Steve,

Thanks for your prompt response. We are passing password with encrypt password due to security reason. Customer is not agreed to put password directly in bcp command . I have encrypted password using ETL tools but while passing encrypt code into bcp it is not working.

sgmunson SSC Guru Points: 110639 More actions · Answer 3

SATYA R - Wednesday, October 24, 2018 8:43 PM
Hi Steve,
Thanks for your prompt response. We are passing password with encrypt password due to security reason. Customer is not agreed to put password directly in bcp command . I have encrypted password using ETL tools but while passing encrypt code into bcp it is not working.

And why would it? Did you check the BCP documentation and discover that it can automagically recognize that the password is encrypted and decrrypt it? Besides, how would it know what encryption method was used? It would have to know, and it would then have to be capable of using the necessary decryption method, AND then know ahead of time that this particular password is indeed encrypted. That's a very impractical expectation. You may need an entirely different type of solution here.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Thom A SSC Guru Points: 99126 More actions · Answer 4

BCP doesn't support encrypted passwords. If you're passing the password in the command, it needs to be the literal value.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sgmunson SSC Guru Points: 110639 More actions · Answer 5

Thom A - Thursday, October 25, 2018 6:54 AM
BCP doesn't support encrypted passwords. If you're passing the password in the command, it needs to be the literal value.

I know that... was just trying to make a point... Unfortunately, my brand of sarcasm is at times, lost on some... no harm done...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Thom A SSC Guru Points: 99126 More actions · Answer 6

sgmunson - Thursday, October 25, 2018 7:35 AM
Thom A - Thursday, October 25, 2018 6:54 AM
BCP doesn't support encrypted passwords. If you're passing the password in the command, it needs to be the literal value.
I know that... was just trying to make a point... Unfortunately, my brand of sarcasm is at times, lost on some... no harm done...

That wasn't aimed at you Steve, it was aimed at the OP. 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sgmunson SSC Guru Points: 110639 More actions · Answer 7

Thom A - Thursday, October 25, 2018 7:42 AM
sgmunson - Thursday, October 25, 2018 7:35 AM
Thom A - Thursday, October 25, 2018 6:54 AM
BCP doesn't support encrypted passwords. If you're passing the password in the command, it needs to be the literal value.
I know that... was just trying to make a point... Unfortunately, my brand of sarcasm is at times, lost on some... no harm done...
That wasn't aimed at you Steve, it was aimed at the OP. 🙂

Oh. okay... sorry...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Thom A SSC Guru Points: 99126 More actions · Answer 8

sgmunson - Thursday, October 25, 2018 7:51 AM
Oh. okay... sorry...

In truth, I imagine this part of the OP's ongoing "saga" of topics on the subject of BCP. I suspect the reason the OP can't use -T is because they are still running BCP on a Linux host and using a push method, rather than a BULK INSERT and a pull method on the SQL Server instance hosted on a Windows Environment (if I recall the chain of topics correctly).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sgmunson SSC Guru Points: 110639 More actions · Answer 9

Thom A - Thursday, October 25, 2018 7:57 AM
sgmunson - Thursday, October 25, 2018 7:51 AM
Oh. okay... sorry...
In truth, I imagine this part of the OP's ongoing "saga" of topics on the subject of BCP. I suspect the reason the OP can't use -T is because they are still running BCP on a Linux host and using a push method, rather than a BULK INSERT and a pull method on the SQL Server instance hosted on a Windows Environment (if I recall the chain of topics correctly).

Yeah, I suspect the OP is "way over his head" in terms of solution design and security ramifications. I recall warning about that with opening a port in the DMZ firewall.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

SATYA R Right there with Babe Points: 715 More actions · Answer 10

Yes, we can't user bulk insert in window system because customer is not ready to open UNC path . I know bulk insert will work some how efficient way this kind of setup but no luck this option is rolled out. I have done different way to pass password. Thanks both of you for your valuable suggestions.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

SATYA R - Wednesday, October 24, 2018 12:29 AM
Hi Guru's,
I have weird situation while passing username and password in BCP command. Can any one let me know how to pass encrypted password in BCP?
Note: We don't have Trusted connection option (-T).
Any inputs?

There's no way that I know of to do such a thing in a BCP command. You should be using "trusted connections" based on the current login.

With that, I have to ask "WHY" you "don't have the Trusted connection option (-T)".

Of course, one option is to make a user that has virtually no privs to do anything except run a stored procedure. The user would execute the stored procedure with parameters that are verified by the stored procedure to prevent SQL Injection and other harmful moves and then to what's necessary in a secure fashion.

And, that would also support what Thom is talking about. It would end up being a "pull" instead of a "push", which is what we've been recommending all along and would solve almost all of your problems with having to deal with the Linux box.

--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)

SATYA R Right there with Babe Points: 715 More actions · Answer 12

I agreed with you reg pull option but customer is not ready to agree this option. I requested below options. Any how we have done different way using etl tools. Thanks for your suggestion.