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

  • 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

  • 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 6 months, 2 weeks ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • Try this command instead:

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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

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

  • do you mean this way? -:

    [CmdletBinding()]

    param (

    $($databasename),

    $($dbpassword),

    $($buildnumber)

    )

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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

  • 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)
    Intro to Tally Tables and Functions

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

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