Post reply

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

  • 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

  • December 13, 2021 at 2:14 pm

    #3963071

    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 2 years, 11 months ago by  Phil Parkin.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 13, 2021 at 2:48 pm

    #3963091

    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

  • December 13, 2021 at 2:51 pm

    #3963093

    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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 13, 2021 at 3:07 pm

    #3963100

    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

  • December 13, 2021 at 3:16 pm

    #3963102

    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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 13, 2021 at 5:47 pm

    #3963149

    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

  • December 13, 2021 at 5:56 pm

    #3963152

    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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 13, 2021 at 6:04 pm

    #3963162

    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?

  • December 13, 2021 at 6:22 pm

    #3963165

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

  • December 13, 2021 at 7:22 pm

    #3963195

    do you mean this way? -:

    [CmdletBinding()]

    param (

    $($databasename),

    $($dbpassword),

    $($buildnumber)

    )

  • December 13, 2021 at 7:29 pm

    #3963198

    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)

  • December 13, 2021 at 8:48 pm

    #3963240

    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

  • December 13, 2021 at 8:51 pm

    #3963242

    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

  • December 14, 2021 at 12:51 am

    #3963295

    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)

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

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