SQLCMD is a great tool. We have certain scripts that uses sqlcmd .The best way to connect to SQL Server database using sqlcmd would be to use Windows Authentication (i.e.; with –E switch).Unfortunately Windows Authentication won’t work everywhere in enterprise when you have distributed databases running in different domain and under different RDBMS.So we had to use SQL Server Authentication and the username and password were hardcoded with sqlcmd.
One of the concern security team had was the username and password are hardcoded in the bat file which execute sqlcmd script and that should be avoided. Application normally saves their setting in registry and then gets the information from there. We were also looking for a way on how to do this with SQLCMD.
One of the article from MSDN which I found very helpful was
This had reference of setting environmental variables and then using it with SQLCMD.Specifcally the note was
If more than one type of variable has the same name, the variable with the highest precedence is used.
1. System level environmental variables
2. User level environmental variables
3. Command shell (SET X=Y) set at command prompt before starting sqlcmd
4. sqlcmd -v X=Y
5. :Setvar X Y
SQLCMD gives variables which can be defined like SQLCMDUSER, SQLCMDPASSWORD, SQLCMDINI etc. The msdn article has complete list of variables which can be set in environmental variables.
Lets start with connecting to SQL instance with SQLCMD.The screenshot shows the default connection which is Windows Authentication
So SQLCMD has used windows authentication and an inbuilt user account to connect to SQL Instance.
Now lets create a sql authenticated id "testsqlcmduser" in SQL Server instance.
Please note I have selected to enforce password policy though its not needed but still its a good practice to enforce password policy in sql authenticated id.
Now lets add this user and its password in system environmental variable.
Go to Control Panel->System->Advanced setting and then click on Environment Variables.You would get a screen like this
You can add the variable in System Profile or current user profile.Click on new button and add the variable SQLCMDUSER and its value as shown below
Similarly add the SQLCMDPASSWORD variable
Click on OK and Apply button.The environmental variables ar all set.Now lets use it with SQLCMD.
Go to command prompt and type sqlcmd and give the SQL Instance Name.
As shown in screenshot above it will get connected .Now lets verify what is the userid which sqlcmd used to connect to SQL Server instance
It has used testsqlcmduser which is the sql authenticated id.How did it worked.We have not even passed the userid and password.Now when you miss -U and -P option sqlcmd checks the system and user profile variable to see if any of the sqlcmd variables are set.If so it uses that to connect to SQL instance.If there are none then it uses windows authentiction to connect to SQL Server instance.
Now lets execute some TSQL command with sqlcmd. I have created a small script file with the following TSQL
use Encryption_Test;
select * from dbo.EncryptedCustomer;
select SUSER_NAME();
Basically it connect to a database Encryption_Test in my local sql instance which was created to test encryption in SQL Server 2008.The script then executes a select command on a table in that database which has some of the encrypted data.The last line returns the current connected user.
Please note we have not specified username or password.So we are hiding the details which are needed to connect to SQL Server instance.Even we can hide the SQL instance name by adding the value in variable SQLCMDSERVER.
