Is it possible to create a Microsoft SMO SQL connection using server credentials in powershell?

user_10625051 0
Viewed 2805

So I have the following code which I believe creates a connection to the local SQL server on my machine using windows credentials.

$ServerObj = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"

Is it possible to specify that I want to use server credentials to connect instead?

i.e. connect using a server username and password rather than my windows credentials

powershell sql-server
3 Answers

I always like this approach, so I don't have to write the password in my scripts..

# Get Credentials
$credential = Get-Credential -username MyAdminAccount -Message "Account credentials"
$username  = $credential.UserName
$password  = $credential.Password
$password.MakeReadOnly()
$creds = New-Object System.Data.SqlClient.SqlCredential($username, $password)

# Create Connection
$SqlConnection.ConnectionString = "Server=$srv; Database=master; Integrated Security=false"
$SqlConnection.Credential = $creds
$SqlConnection.Open()

# SQL Statement 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandTimeout = 0
$SqlCmd.CommandText = 'Select * from yourTable' 
$SqlCmd.Connection = $SqlConnection
user_23085970

You can so something like this - 

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server(SourceServerName);  #New SMO Object
$db = New-Object Microsoft.SqlServer.Management.Smo.Database;   #New Database Connection Object
$db = $srv.Databases.Item(SourceDatabaseName);  #Database Information stored in $db

If you have credentials, then you can build a connection string as follows - 

$ConnectionString = server=*serverName*;database=*databaseName*;user id=*userName*;password=*passWord*;  #For SQL Credentials
$Connection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)

or

$ConnectionString = "server=*serverName*;database=*databaseName*;user id=*domain\username*;password=*passWord*;trusted_connection=true;"   #For Domain credentials
$Connection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)

But the above method is highly susceptible to SQL Injection. If you are little/not at all concerned over security then you can try something like the aforementioned.

You can also look at other solutions, specially the PowerShell module dbatools here. Hope this helps!

user_69472810

You can use the following:

$server = new-object Microsoft.SqlServer.Management.Smo.Server(DATA_SOURCE)
$conContext = $server.ConnectionContext
$conContext.LoginSecure = $false
$conContext.Login = LOGIN_NAME
$conContext.Password = LOGIN_PASSWORD
$db = $server.Databases.Item(DATABASE_NAME)
user_81928880
Related