Connect to SQL server using different AD user using powershell

Question

WE are having an issue with connecting to SQL server using active directory domain user (AD User ) to SQL server instance 2016. Its automatically pickup login local user NOT the new AD user(prod\Aduser1). not sure parameter are correct

$pass = 'Gvtddt6'
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = “Server=SQLSERVER;TRUSTED_CONNECTION=TRUE;Encrypt=true;TrustServerCertificate = true;Initial Catalog=master;UID=domain\ADuser1;PWD=$pass;Integrated Security=true;”
$sqlConn.Open()
$sqlcmd = $sqlConn.CreateCommand()
$query = “ SELECT TOP (2)[equi_nce]
              FROM [test].[table1]”
$sqlcmd.CommandText = $query
$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
$data = New-Object System.Data.DataSet
$adp.Fill($data) | Out-Null
$data.Tables

any one can help would be great

thanks

Answer 1

First of all, you are saying TRUSTED_CONNECTION=TRUE. That means Windows authentication. Which means the current user.

Next, even if you take out TRUSTED_CONNECTION, this part UID=domain\ADuser1;PWD=$pass is not going to work out as intended. When you specify UID+PASS, this always means SQL authentication. That is, the user must be an SQL login. In cannot be Windows user.

When you log on to SQL Server with Windows authentication, you always do that with your current Windows user. If you want to connect as a different Windows user, you first need to perform a login into Windows, for instance with the RUNAS command. (It is also possible that PowerShell provides means for this, but that beyond the scope of the tag SQL Server.

Answer 2

Hi @Ashwan,

You need to add this Authentication=ActiveDirectoryPassword;

You need to choose your authentication method. It corresponds to the one here.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

@Ashwan, Did you get solution?