Windows Authentication Error

kiyoharakiyohara Posts: 5
edited February 9, 2006 1:20PM in SQL Compare Previous Versions
I'm trying to compare two servers that are in separate domain controller-less clusters (cluster A and cluster B). I am running SQL Compare from a domain controlled workstation (domain C). A, B and C all have accounts with the same username/password.

I can successfully connect with A and B with SQL Server Management Studio using Windows Authentication. SQL Compare appears to work partially since the "Comparison Settings" dialog is able enumerate the databases on A and B. When I click the compare button, I get an "Unknown" dialog: Cannot open database "xxx" requested by the login. The login failed. Login failed for user 'A\uuuu'.

One other note: the servers in the "Comparison Settings" are specified using the IP address and port: "192.168.10.99,12345".
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello there,

    Is this a problem with the Windows accounts? I would imagine that it would not work, as in Management Studio, to put a Windows account into SQL Compare. With SQL Compare's connection dialogue, you can only specify a user name when using SQL authentication. Windows authentication will pass your current credentials from the logged in user to the SQL Server. With Windows Authentication, you cannot specify the user and account details like you can with SQL Authentication.

    Hopefully this is the issue.
    · Share on Twitter
  • kiyoharakiyohara Posts: 5
    I'm only using Windows Authentication. For most Windows applications, including Management Studio, using the same username/password combination will work regardless of domain. This is a common technique supported by Windows to get around problems with multiple domains. For example, setting up accounts for each domain:

    user: domainA\joe; pass: foo
    user: domainB\joe; pass: foo
    user: domainC\joe; pass: foo

    will allow domainA\joe to access most things domainC\joe can without having to re-login.

    As I mentioned previously, SQL Compare is able to connect successfully in the "Connection Settings" dialog. Otherwise it wouldn't be able to obtain the list of databases for each server to populate the Database combo box. It's only when I hit "Compare" that something is failing.

    Thanks for you quick reply,
    -Keith.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Keith,

    Yes, SQL Server does support Windows 'matching accounts' to pass the windows account information to allow you to log into the server with Windows Authentication across a network or even the Internet. As far as I know, though, specifying SQL authentication forgoes the Windows authentication.
    · Share on Twitter
  • kiyoharakiyohara Posts: 5
    Hello Brian,

    I'm only using Windows Authentication. I am only using Windows Authentication in SQL Compare and SQL Management Studio.

    SQL Authentication is not being used. I am not using SQL Authentication in SQL Compare or SQL Management Studio.

    As I mentioned in my earlier posts, SQL Compare fails to open the databases when using Windows Authentication.

    -Keith.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Keith,

    Thanks. Regarding your domain-less cluster configuration, are you running SQL Compare on one of the cluster nedes, then attempting Windows Authentication to the Virtual Server? If this is SQL 2005, is there a named instance of SQL Server on the virtual server? I'd like to see if I can reproduce the login failure and come up with a workaround.
    · Share on Twitter
  • kiyoharakiyohara Posts: 5
    My original post used an unfortunate choice of words in referring to "clusters". We are not using any of the MS clustering technologies on our SQL Server 2005 Standard machines.

    The configuration for this problem is:
    • SQL 2005 Standard on stand-alone W2K3 server "A", no domain, IP=192.168.10.100, TCP Port=12345
    • SQL 2005 Standard on stand-alone W2K3 server "B", no domain, IP=192.168.20.100, TCP Port=12345
    • SQL Compare on XP workstation "C", in a domain

    When using SQL Compare "Comparison Settings" dialog, I specify the two servers as:
    • 192.168.10.100,12345 (Windows Authentication), Database=Foo
    • 192.168.20.100,12345 (Windows Authentication), Database=Foo

    The fact that I can select database "Foo" from the combobox below each server choice means that SQL Compare is correctly contacting each server.

    When I click the compare button, I get an "Unknown" dialog: Cannot open database "Foo" requested by the login. The login failed. Login failed for user 'A\joe'.

    -Keith.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Keith,

    I've set up a similar scenario here with matching accounts and they do seem to work in SQL Compare. I have a local user on my SQL Server, BRIAN\JOE. I run SQL Compare on another workstation, also logged in as a local user called JOE. I can compare databases using the Windows Authentication setting, not specifying a SQL authentication user and password.

    If you can't get this working, then check the local account on both workstations. Make sure that the passwords are the same for both user accounts. Also check that the accounts aren't locked out or anything like that.

    Don't forget to check SQL Server and make sure that the user has access to the database that you want to compare!
    · Share on Twitter
Sign In or Register to comment.