BCP and encrypted password
-
I have a problem,
I have run a series of bcp statement (in automatic way, running a stored proc), to run they need password.
The problem is:
how can pass the encrypted password from sql to BCP with the command :
EXEC @RetError = master..xp_cmdshell @RunCmd
where @runcmd is the bcp command: bcp "celect * from table1" queryout "c:\pippo.dat" -n -S . -U sa -P encrypted password
Because I don't want to leave password as clear text in the procedure.
Thank
Any help will be appreciated
-
Use trusted connection (-T).
-
And don't use "sa".
_____________
Code for TallyGenerator -
Hi,
I used - T arguments, but:
if the bcp is run directly on the windows command prompt, it works.
if the bcp is run from sql : EXEC @RetError = master..xp_cmdshell @RunCmd from the stored proc, the following error occur:
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\SYSTEM'.
NULL
Do you vave any suggestions?
-
Check the account started MSSQLSERVER.
It must be "Local System Account".
Change it to proper account and try not to use Local System Account to start MSSQLSERVER anymore.
_____________
Code for TallyGenerator -
My config are:
SQL Server properties (configure) local (tab: security):
- SQL server authentication
Start service account: Start and run SQL server in the following account:
- system account.
Registered SQL Server Properties:
Use SQL server properties
- Use SQL server authentication
-
System account is not the right account to start SQL server. The are many issues corresponding to this option. If you want you can make a quick research and find a lot of articles about this.
If you run SQL Server from, say, your account you'll neve get that issue.
_____________
Code for TallyGenerator -
SQL Server service account should not be LocalSystem nore local computer accounts. They need to be domain user acconuts that are in the LocalAdministrators security group. As an additional security best practice you remove 'interactive logon' from these service accounts. As for passing bcp an 'encryptd ' password, there is no such support for this. Your best bet is using the -T flag (trusted connection). If you set up your services as stated earlirt, then you should have no issues.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
You can create stored procedure with encription.
-
Just a quick thought on 'encryption' ... there are lot's of 'freebies' on the web for 'decryption' and a few 'pay for' packages as well. In SQL 2000 encryption is definitely NOT 'hack-proof'. If you are going to want something like this you'll have to wait for SQL 2005. It's enryption is much more solid and comprehensive.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Just a quick thought... why does the BCP login/password need rights to anything but the destination table? Make a user that only has rights to the input staging table (you ARE using a staging table, aren't you?) just for this BCP job. Once you've transferred the data out of the staging table, truncate it to remove the data and then who cares if someone get's the login?
--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) -
create proc p_bcpPassword @pwd varchar(15) output WITH ENCRYPTION
as
select @spwd = 'bcppasswordhere'
return
EXEC Master..P_BcpPassword @SPwd out
select @command = 'bcp.............-P' + @spwd + '........'
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply