Compression Analyzer - Authentication Bug

KatanaCSKatanaCS Posts: 24 Bronze 2
edited March 11, 2008 4:31PM in SQL Backup Previous Versions
Hi,

I'm not 100% sure if this post should be here or somewhere else, but I couldn't find a defined group for Compression Analyzer.

I just tried to use the Compression Analyzer to estimate a database, and received an authentication error:

Error running test: SQL error -2147467259: Login timeout expired
SQL error -2147467259: Login timeout expired
SQL error 2: Named Pipes Provider: Could not open a connection to SQL Server [2].
Server REPORT2K5N1
Windows authentication

@ServerName value is the same as the virtual server, not the cluster node.

The error states that it is attempting to connect to the SQL Server on the node which currently owns the cluster, as opposed to name of the virtual server. CA will connect to the correct server to retrieve the database list, but when you try to start the test, it uses the controlling node as the server name.

I tried this local on the server with both Windows Authentication and SQL Authentication (both of which have sysadmin access to SQL Server), as well as trying it remotely from my machine (with SQL Authentication only, as a sysadmin). I also tried it on another cluster we have, and I tried it with different database sizes, ranging from ~100MB to ~100GB. I also tried leaving the default database in place (which is only the first one sorted alphabetically) instead of changing it to another database name. I also tried launching CA from the SQLBackup GUI as opposed to just the EXE. The error was consistent in all cases.

Steps to reproduce:
1 - Launch CA on a cluster node.
2 - Change the default value in the "SQL Server:" field from the node name to the virtual server name.
3 - Choose appropriate authentication method.
4 - Change database drop-down to any database (or leave alone).
5 - Click on "Start Test". "Status" changes to "Running ..." and the database size is reported appropriately, but after the login timeout expires, the login error dialog appears.

Thanks!
Chris
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    I could not reproduce the error described in your post.

    Could you please run Profiler, and see if there are 2 connections established by the Compression Analyzer to the server? One connection runs the master..sqbtest command, while the other retrieves the test status by running the master..sqbteststatus command.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • KatanaCSKatanaCS Posts: 24 Bronze 2
    Sure. The first database in the list alphabetically is [ABI], and I changed the database in the list to be [active2_repl]. The SQL login I used is "DBA". Of course, since there's no SQL instance installed with the name of the local node (the error being thrown), I can't run a trace on it to see where CA is trying to connect to that instance.

    Here's a capture of the profiler output (pipe-delimited):
    Event|TextData|login|spid
    Audit Login||DBA|150
    SQL:BatchStarting|"SELECT OBJECT_ID('sqbtest') sqbtest, OBJECT_ID('sqbteststatus') sqbteststatus, OBJECT_ID('sqbtestcancel') sqbtestcancel"|DBA|150
    SQL:StatementStarting|"SELECT OBJECT_ID('sqbtest') sqbtest, OBJECT_ID('sqbteststatus') sqbteststatus, OBJECT_ID('sqbtestcancel') sqbtestcancel"|DBA|150
    SQL:StatementCompleted|"SELECT OBJECT_ID('sqbtest') sqbtest, OBJECT_ID('sqbteststatus') sqbteststatus, OBJECT_ID('sqbtestcancel') sqbtestcancel"|DBA|150
    SQL:BatchCompleted|"SELECT OBJECT_ID('sqbtest') sqbtest, OBJECT_ID('sqbteststatus') sqbteststatus, OBJECT_ID('sqbtestcancel') sqbtestcancel"|DBA|150
    RPC:Starting|exec sp_reset_connection|DBA|150
    Audit Logout||DBA|150
    RPC:Completed|exec sp_reset_connection|DBA|150
    Audit Login||DBA|150
    SQL:BatchStarting|SELECT name FROM master..sysdatabases WHERE status & 1568 = 0 ORDER BY name|DBA|150
    SQL:StatementStarting|SELECT name FROM master..sysdatabases WHERE status & 1568 = 0 ORDER BY name|DBA|150
    SQL:StatementCompleted|SELECT name FROM master..sysdatabases WHERE status & 1568 = 0 ORDER BY name|DBA|150
    SQL:BatchCompleted|SELECT name FROM master..sysdatabases WHERE status & 1568 = 0 ORDER BY name|DBA|150
    RPC:Starting|exec sp_reset_connection|DBA|150
    Audit Logout||DBA|150
    RPC:Completed|exec sp_reset_connection|DBA|150
    Audit Login||DBA|150
    SQL:BatchStarting|USE [ABI] SELECT SUM(CAST(size AS BIGINT) * 8192) size FROM sysfiles|DBA|150
    SQL:StatementStarting|USE [ABI] |DBA|150
    SQL:StatementCompleted|USE [ABI] |DBA|150
    SQL:StatementStarting|SELECT SUM(CAST(size AS BIGINT) * 8192) size FROM sysfiles|DBA|150
    SQL:StatementCompleted|SELECT SUM(CAST(size AS BIGINT) * 8192) size FROM sysfiles|DBA|150
    SQL:BatchCompleted|USE [ABI] SELECT SUM(CAST(size AS BIGINT) * 8192) size FROM sysfiles|DBA|150
    RPC:Starting|exec sp_reset_connection|DBA|150
    Audit Logout||DBA|150
    RPC:Completed|exec sp_reset_connection|DBA|150
    Audit Login||DBA|150
    SQL:BatchStarting|USE [active2_repl] SELECT SUM(CAST(size AS BIGINT) * 8192) size FROM sysfiles|DBA|150
    SQL:StatementStarting|USE [active2_repl]|DBA|150
    SQL:StatementCompleted|USE [active2_repl]|DBA|150
    SQL:StatementStarting|SELECT SUM(CAST(size AS BIGINT) * 8192) size FROM sysfiles|DBA|150
    SQL:StatementCompleted|SELECT SUM(CAST(size AS BIGINT) * 8192) size FROM sysfiles|DBA|150
    SQL:BatchCompleted|USE [active2_repl] SELECT SUM(CAST(size AS BIGINT) * 8192) size FROM sysfiles|DBA|150
    RPC:Starting|exec sp_reset_connection|DBA|150
    Audit Logout||DBA|150
    RPC:Completed|exec sp_reset_connection|DBA|150
    Audit Login||DBA|150
    SQL:BatchStarting|master..sqbtest N'active2_repl'|DBA|150
    SQL:StatementStarting|master..sqbtest N'active2_repl'|DBA|150
    Audit Login||DBA|153
    SQL:BatchStarting|master..sqbteststatus|DBA|153
    SQL:StatementStarting|master..sqbteststatus|DBA|153
    SQL:StatementCompleted|master..sqbteststatus|DBA|153
    SQL:BatchCompleted|master..sqbteststatus|DBA|153
    |
    The above 4 lines are repeated 19 times
    |
    |153
    SQL:StatementCompleted|master..sqbtest N'active2_repl'|DBA|150
    SQL:BatchCompleted|master..sqbtest N'active2_repl'|DBA|150
    this is where the error dialog pops up
    once the app is closed, the next two lines show up
    Audit Logout||DBA|150
    Audit Logout||DBA|153

    I also included errors in the trace, and only the warnings of changing database context and language setting were reported.

    Thanks!
    Chris
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    The Compression Analyzer appears to be connecting to the target server correctly, since profiler logged 2 distinct SPIDs created by it. It is the SQL Backup Agent service that is attempting to connect to the wrong instance.

    Are you able to perform SQL Backup backups/restores on this cluster?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • KatanaCSKatanaCS Posts: 24 Bronze 2
    Sorry, just realized I wasn't watching this post.

    Yes, we're performing both backups and restores on this server every day.
    Chris
    · Share on Twitter
Sign In or Register to comment.