Is it possible to configure SQL Server Agent to connect to SQL Server using SQL Server authentication?

  • Article

KB Article 943525 (You cannot start the SQL Server Agent service of a failover cluster of SQL Server 2005 if the build of SQL Server build is 3179 or a later build), in its first revision (November 21, 2007), it enumerated three different workarounds, one of which (Method 1) suggested configuring SQL Server Agent to connect to SQL Server using SQL Server authentication and sa as the login.

 Method 1

  1. Start SQL Server Management Studio, and then connect to the instance of SQL Server 2005.
  2. In Object Explorer, right-click the instance of SQL Server 2005, and then click Properties.
  3. Under Select a page, click Security.
  4. Under Server authentication, click SQL Server and Windows Authentication mode, and then click OK.
  5. Configure the SQL Server Agent service to connect to SQL Server by using the sa account. To do this, right-click SQL Server Agent, and then click Properties. In the SQL Server Agent Properties dialog box, click Connection. Under SQL Server Connection, click to select the Use SQL Server Authentication to connect to a SQL Server database instance using sa account check box.
  6. Open the Cluster Administrator snap-in.
  7. Expand Groups.
  8. Right-click the SQL Server resource, and then click Bring Offline.
  9. Right-click the SQL Server resource, and then click Bring Online.
  10. Right-click the SQL Server Agent resource, and then click Bring Online.

You might have tried this workaround several times without success, right? The reason why you cannot implement this is very simple: starting with SQL Server 2005 RTM, it is NOT possible to configure SQL Server Agent to connect to SQL Server using SQL Server authentication.

Following is the explanation of how I concluded that this method wasn’t something doable:

I do have access to the source code of the product, but since I cannot expose it here, I will use the tools anyone could use to find this on his own, without having any special privileges. For such purpose, I'll be using .Net Reflectorwritten by Lutz Roeder.

By using .Net Reflector, I disassembled the InitProperties method of the SqlServerAgentPropertiesConnection class, which is part of the Microsoft.SqlServer.Management.SqlManagerUI namespace and is implemented in %programfiles%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlManagerUI.dll. Why that class? With some debugging skills, the Debugging Tools for Windows, the sos.dll debugging extension which comes with the .Net Framework, and a bit of patience, it isn't difficult to find out that it is that particular class the one which implements the code for the Connection properties tab for SQL Server Agent dialog in SQL Server Management Studio. As you can see below, the InitProperties method forces the selection of the Windows Authentication radio button, and disables all the controls under the "SQL Server connection" separator, when it's administering a SQL Server 2005, and that's because, based on the functional specifications, SQL Server Agent in Yukon only can connect to SQL Server using Windows Authentication.

private void InitProperties()
{
try
{
JobServer server = base.get_DataContainer().get_Server().get_JobServer();
if (base.get_DataContainer().get_Server().get_Information().get_Version().Major < 9)
{
.
.
.
        }
else
{
this.radioWinAuth.Checked = true;
this.radioWinAuth.Enabled = this.radioSQLAuth.Enabled = this.comboLogin.Enabled = false;
this.textPassword.Enabled = this.labelLogin.Enabled = this.labelPasswd.Enabled = false;
}
.
.
.
}

I also went through the SQL Server Agent's source code and noticed that the method from where it prepares the connection string, it doesn’t honor the RegularConnections registry setting as it did in previous versions, but it always uses Windows authentication instead.

Finally, I had a look at a couple of pages from the documentation (http://technet.microsoft.com/en-us/library/ms178121.aspx and http://msdn2.microsoft.com/en-us/library/ms189326.aspx) to find that what was mentioned in them, matched what I had already seen in the code. So, there was no error in the code, neither in the documentation, but it was a mistake in the article. Therefore, I have submitted a change request, which will be publicly available pretty soon.

How useful do you find these posts? Would you like to eliminate one specific doubt about how a given product feature actually works? Would you prefer me to offer some other type of contents? Please leave your opinion in the form of a comment, so that I can help you better.

See you in the next post!