What are the challenges you face when working across database platforms? Take the survey
Options

SQL Compare Command line vs UI database access

I'm writing a script to compare and sync up multiple database using filters by type of object.
when I run the script 2 out of 8 database return "Error: Cannot open database "xyz" requested by the login. The login failed. Login failed for user 'Domain\UserID'.

I'm using my Windows Authentication account.  All other 6 databases have not problem.

But when I used the GUI version of SQL Compare, I can connect to all database and compare them all without issues.


Tagged:

Answers

  • Options
    Hi, thank you for reaching out regarding this concern. 

    If possible can you share your script so we can help investigate this issue?
    To start looking into this we'd like to verify the script just to verify there isn't a scripting issue which we can help rectify quickly.

    Are you able to also verify that the database is configured for Windows authentication and that the user you are trying to log into the database with has appropriate access permissions?



    Jon Kirkwood | Technical Support Engineer | Redgate Software
  • Options
    SuperMarioSuperMario Posts: 3 New member
    Jon,

    As stated originally, I'm using Windows Authentication, I have SysAdmin rights to the server/instance, I can access the database using windows authentication from SSMS and with SQL Compare GUI, but not from the command line.

    the scripts I'm running look like this

    SQLCompare.exe /s1:"server1" /db1:mydatabase        /s2:server2 /db2:mydatabase         /filter:C:\workshop\script\TablesUDT.scpf    /ScriptFile:"C:\Workshop\Script\HmgLakeTablesUTDUpdate.sql"

    I'm running compares for 11 databases, 3 times each using different filters.  of the 11 database 2 give me error mentioned above, regardless of the filter they are running with.

    Any suggestions are welcome

    Thanks,
    Mario Hernandez
  • Options
    SuperMarioSuperMario Posts: 3 New member
    I found the issue! and it was a PICNIC ( problem in chair not in computer).
    Someone decided to change the spelling of these 2 databases to plural instead of single, which I didn't noticed on the GUI when selecting the databases, so the script was trying to connect to a non-existent database.

    Please disregards this posting ( delete it if you can to protect the innocents :)  ) 
  • Options
     (~) Your secret is safe with us.
    Glad you found the culprit and were able to resolve the issue.

    Thank you for posting up and sharing the details.
    Jon Kirkwood | Technical Support Engineer | Redgate Software
Sign In or Register to comment.