Restore DB and create a new SQL login

DSC
#1

Hi,

I used xSqlServerInstall (from model xsqlps) and xSQLServerLogin (from model xSQLServer) resources to install an sql server and to create an new sql login but there is no property for assigning a password. Do I have to build my own resource for that? Also wanted to know which resource to use to restore a database from a .bak file?

Thanks in advance
Mindaugas

#2

You’d need to, use. But, in an ideal world (from Microsoft’s perspective), you’re using Windows logins, not SQL Server logins, so you don’t need to set passwords.

#3

Haven’t tried it myself as I lack the environment to (rebuilding my entire home net from scratch), but xSQLServerLogin has a
parameter $LoginCredential of type System.Management.Automation.PSCredential which means in your configuration command you can
set a -LoginCredential and supply it with something like this:

$secpasswd = ConvertTo-SecureString “SQLLoginPassword” -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential (“username”, $secpasswd)

where username is actually the SQLServerlogin name, thus you use -LoginCredential $mycreds

do note that the Test section of the DSC resource doesn’t implement validation of the password so you cant use the password as
a point of decision if a configuration change has happened.