August 26, 2025 at 8:05 pm
We have a few stored procs where we need to a dos command "net use"
EXEC master..xp_cmdshell 'NET USE \\<servername>\<filesharename> /USER:<domain\username> <current hardcoded pwd> /PERSISTENT:yes'
I am trying to figure out a way to retrieve a password stored somewhere that is obfuscated from nefarious people, via encryption or any other way really, So that I can replace the <current hardcoded pwd> with a value retrieved in the above statement
I have looked at this link and did the tutorial
I am able to see the decrypted SSN in the example IF I connect, setting the "Enable Always Encrypted (column encryption). and Enable Parameterization on Always Encrypted in the Options of Query view (Options -> Execution-> Advanced)
The problem with that is I don't know if the connection will have those settings turned on and it seems like a bit of security risk if someone hacks in.
`
use [ContosoHR]
go
select * from hr.employees
Declare @ssn char(11)
select @ssn = ssn from hr.employees
where EmployeeID = 1
Select @ssn
--works up to here
--print @ssn
----error Operand type clash: char(11) encrypted with (encryption_type = 'DETERMINISTIC'
----, encryption_algorithm_name = 'ddd_xxx_444_ttt_hhhh_rgt_256'
----, column_encryption_key_name = 'CEK_Auto1'
----, column_encryption_key_database_name = 'ContosoHR')
----collation_name = 'SQL_Latin1_General_CP1_CI_AS' is incompatible with nvarchar
if @ssn = '795-73-9838'
----Encryption scheme mismatch for columns/variables '@ssn'.
----The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC',
----encryption_algorithm_name = 'ddd_xxx_444_ttt_hhhh_rgt_256'
----, column_encryption_key_name = 'CEK_Auto1'
----, column_encryption_key_database_name = 'ContosoHR')
----and the expression near line '13' expects it to be (encryption_type = 'PLAINTEXT') (or weaker).
begin
print 'a match'
END
`
Reminder the Net Use command is in a stored proc.
Any help is appreciated.
August 27, 2025 at 9:00 am
It seems strange to do this in a SP; I would be more inclined to have something on an application server and use a more suitable language. (C#)
If you really feel use need to do this in a SP, why not change the SQL Server service account to a domain service account which also has permissions on the share you want to access? The user and password will then not be needed with NET USE.
August 30, 2025 at 5:15 am
I try to avoid hardcoding passwords in stored procs. The safest route in my experience is to run SQL Server or the job under a domain account that already has access to the share, so no password is needed at all.
If I absolutely have to store one, I use a SQL Credential or encrypt it with a certificate and only decrypt it when building the NET USE command. That way the cleartext password only exists in memory for a moment.
Always Encrypted isn’t a good fit here—it’s meant for protecting data at the client layer, not for retrieving service passwords inside SQL Server.
September 2, 2025 at 7:00 pm
Can you set a proxy account? As noted above, I'd try to use an account here, not a user/pwd.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply