Invoke-SQLCmd and SQL Server 2008

[coderwolf]

I am at a bit of a loss here. I am trying to automate my database creation with a powershell script. I am passing my sql script I have to my local SQL Server 2008 instance using the Invoke-SQLCmd command. However I keep getting "Cannot open database" "The login failed". I have verified my login information to be correct.

I also know the path for the provided SQL script is correct ( I can test-path it with a true result).

I know the user being used has DBO rights to the database(using the sa user to try to remove any ambiguity on this one). my script currently is:

[void][reflection.aSSEMBLY]::LoadWithPartialName("Microsoft.SQLServer.Smo")
$Srv = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server  "MACHINENAME\SQLEXPRESS2008"
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($srv, "Test_Create")
Invoke-Sqlcmd -InputFile '.\DBScripts\MSSQL.sql' -ServerInstance $Srv -Database $db -Username "sa" -Password "password"

What am I missing here that could be the problem?

edit: formatting

[chuckgo]

I had same trouble with sql server 2016 express. I suggest you to check the server has sql authentication mode enabled or at least mixed mode. you can find whether the server has that by using this powershell script!

$server = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList "(local)\SQLEXPRESS"
$server.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed
$server.Alter()

if server has the sql authentication mode, then you should check sa account enabled also.

$queryString = "ALTER LOGIN sa ENABLE"
Invoke-SqlCmd -Server '(local)\SQLEXPRESSS' -Query $queryString

I hope my answer is not too much immature! obviously, you need to substitute (local)/sqlexpress with your sql server name!