invoke-sqlcmd : Cannot open database requested by the login. login. Login failed

Question

Post reply

invoke-sqlcmd : Cannot open database requested by the login. login. Login failed

MillionQueries

Old Hand

Points: 369
More actions
December 13, 2021 at 2:12 pm

#3963069

Hello,
I am not able to figure out why invoke-sqlcmd is not working. It’s giving login failed error..This user has sysadmin permissions.
When the azure release pipeline runs, if the database does not exist then it restores the new database from the .bak file....and i am using invoke-sqlcmd command for that......when i try to run the select statement using invoke-sqlcmd it works fine but while doing the db restore it does not seem to work and i cannot figure out why.....
The error is below -:
“Invoke-sqlcmd - cannot open database requested by login. The login failed”
I googled the error but nothing seems to be working.
Any help will be appreciated.
Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

What is the default database for the login which you are using? Is it master?

Can you give us the full command you are trying, sanitised if necessary?

This reply was modified 3 years ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

MillionQueries Old Hand Points: 369 More actions · Answer 2

Thank you for your reply Phil.

Yes, i am using master as default. below is the code -:

$sql = @"

USE [master]

RESTORE DATABASE $dbname FROM DISK = N'$fullpath' WITH FILE = 1, MOVE N'NRI_Data' TO N'G:\Database\$dbname.mdf', MOVE N'NRI_Log' TO N'L:\Log\$dbname.ldf', NOUNLOAD, STATS = 5

GO

"@

invoke-sqlcmd -ServerInstance $server -Database $dbname -Username $sqlusername -Password $sqlpassword -Query $sql

Thank you

Phil Parkin SSC Guru Points: 246988 More actions · Answer 3

Try this command instead:

invoke-sqlcmd -ServerInstance $server -Database master -Username $sqlusername -Password $sqlpassword -Query $sql

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

MillionQueries Old Hand Points: 369 More actions · Answer 4

Hi Phil,

That worked like a charm. Thank you so much, really appreciate that.

So, when i used $dbname instead of Master, does it assume that the database should exist? is that why it was failing?

Thank you

Phil Parkin SSC Guru Points: 246988 More actions · Answer 5

MillionQueries wrote:

So, when i used $dbname instead of Master, does it assume that the database should exist? is that why it was failing?
Thank you

Correct. It was trying to run the RESTORE command in the context of $dbname, a bit like doing this

USE $dbname
RESTORE $dbname

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

MillionQueries Old Hand Points: 369 More actions · Answer 6

Hi Phil,

In my release pipeline i am trying to run the powershell script using "File path". In the arguments section i have passed what was defined in the variables section -databasename $(databasename) -dbpassword $(dbpassword) and i also added these parameters in the powershell script. A portion of the script is below -:

[CmdletBinding()]

param (

$databasename,

$dbpassword,

$buildnumber

)

#Looping through the database names specified in variables

$databaseinput='$(databasename)'

$multipledb=$databaseinput.Split(",")

foreach ($db in $multipledb)

{

#Declare and define variables

$server = 'nrisql2'

$dbname=$db

$sqlusername = 'davedutch'

$sqlpassword = '$(dbpassword)'

$buildnumber = '$(build.buildnumber)'

#Split the variable so it finds the hyphen from the string and use the left side of the string

$partialbuildnumbersplit = $buildnumber.split('-')[-2]

#Always skip first 4 characters NRI_

$partialbuildnumber= $partialbuildnumbersplit.Substring(4)

but when i try to run the pipeline it gives me the following error -:

"You cannot call a method on a null-valued expression"

Not sure what i am doing wrong. Can you advise?

Thank you

Phil Parkin SSC Guru Points: 246988 More actions · Answer 7

Why are you putting parentheses around some of your variable names? I'm not sure that they're needed.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

MillionQueries Old Hand Points: 369 More actions · Answer 8

$(databasename), and $(dbpassword) are defined in pipeline variables. I wanted to use that in the powershell script thats why the parenthesis around them
$(build.buildnumber) - I have used it to dynamically take the current build number

while using file path instead of inline, does it behave differently?

frederico_fonseca SSCoach Points: 16167 More actions · Answer 9

frederico_fonseca

SSCoach

Points: 16167

December 13, 2021 at 6:22 pm

#3963165

use $($variablename) to see if it solves your issue.

MillionQueries Old Hand Points: 369 More actions · Answer 10

do you mean this way? -:

[CmdletBinding()]

param (

$($databasename),

$($dbpassword),

$($buildnumber)

)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 11

Why are you doing this in PowerShell?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MillionQueries Old Hand Points: 369 More actions · Answer 12

Hi Jeff,

Thanks for your reply. The script i included here is just a part of the long script. I did not see any other way to achieve what i was trying to do.

I wanted to restore a database from the backup file and run another powershell script which accepts parameters.

Did you have any other suggestion in mind which could have made it easier?

Thank you

MillionQueries Old Hand Points: 369 More actions · Answer 13

I was able to resolve the issue.

I had to use $env variable for this to work.

$databaseinput=$env:DATABASENAME

$sqlpassword=$evn:DBPASSWORD

$buildnumber=$env:BUILD_BUILDNUMBER

Thank you

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 14

MillionQueries wrote:

Hi Jeff,
Thanks for your reply. The script i included here is just a part of the long script. I did not see any other way to achieve what i was trying to do.
I wanted to restore a database from the backup file and run another powershell script which accepts parameters.
Did you have any other suggestion in mind which could have made it easier?
Thank you

What's wrong with a simple stored procedure on the server in question?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)