Compression Analyzer - Authentication Bug

KatanaCS

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!

petey

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.

KatanaCS

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!

petey

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?

KatanaCS

Sorry, just realized I wasn't watching this post.

Yes, we're performing both backups and restores on this server every day.