Tag: SQL-Server

3 Posts Found
 

SQL Server Connection Error: The Kerberos client received a KRB_AP_ERR_MODIFIED error from the server

Posted: 12/31/2016 11:47 AM

I recently changed the user account that my SQL Server instance was running under from "sql.user" to "sql.user2". When I attempted to have my application server connect via integrated Windows Authentication to this SQL Server after changing the user account and restarting the service, I began receiving this error message in the Windows System Event Log:

The Kerberos client received a KRB_AP_ERR_MODIFIED error from the server sql.user2. The target name used was MSSQLSvc/db01.xyz.com:52663.

I discovered after some research that the client server was still attempting to connect to my SQL Server using the old account name. Running the PowerShell command setspn -Q MSSQLSvc/db01.xyz.com:52663 yielded the following output, indicating that the old account was still being used.

Checking domain DC=xyz,DC=com
CN=SQL Server Service Account,OU=Service Accounts,DC=xyz,DC=com
    MSSQLSvc/db01.xyz.com:52663

To clear the cached kerberos entries, I ran the following commands on the client computer:

setspn -D MSSQLSvc/db01.xyz.com:52663 sql.user
setspn -S MSSQLSvc/db01.xyz.com:52663 sql.user2

This cleared out the cached entries and I was able to reconnect.

 

SQL Server 2008R2 Cluster on Windows Server 2012R2

Posted: 4/21/2016 9:23 AM

My firm is migrating our SQL Server 2008 R2 Cluster to new infrastructure. Our first attempt is to set it up running Windows 2012 R2 Server, a difference from our current Windows 2008 R2 Server environment.

When I attempt to run the SQL 2008 R2 installer to create a new Failover Cluster, the Setup Support Rules check gives me errors about the cluster, that the Cluster Service and Cluster Storage aren't available.

It turns out that SQL 2008 R2 relies on some Windows cluster functionality that is not installed by default in Windows 2012 R2, specifically the Failover Cluster Automation Server.

You can open a PowerShell prompt in Administrator mode and execute the following:

    Get-WindowsFeature RSAT-Cluster*

You'll see that the Failover Cluster Automation Server is not checked off. To install it, execute the following:

    Install-WindowsFeature -Name RSAT-Clustering-AutomationServer

If you quit your SQL Installer and attempt to re-run it to create a Failover Cluster, you'll see that the setup support rules around the cluster now pass.

Next problem: Windows Server 2003 FILESTREAM Hotfix Check: failed

This is a simple fix: Copy c:\Windows\Cluster\Clusres.dll to c:\Windows\system32 and rename the file to W03a2409.dll

The last of the setup support rules should pass.

 

SQL Server String List Parse Function

Posted: 3/14/2016 4:24 PM

Most modern web languages these days offer some function to parse a "list" of text items, which is usually a text string with multiple values separated by a delimiter, often a comma. SQL Server unfortunately does not have such an affordance, which becomes troublesome when you're passing a dynamic number of parameters into a SQL statement.

Here comes a user-defined function, which takes a string and a delimiter, and returns a table with the text items broken out into separate rows. To use it:

    select t.theString
    from dbo.fn_ParseStringList('1,2,3', ',') t
    order by t.theString

And here's the code:

    create function [dbo].[fn_ParseStringList] (
      @input varchar(8000),
      @delim char(1)
      )
    returns @output table (
      theString varchar(200)
      )
    as
    begin
      -- append the delimiter to the end of the string
      set @input = @input + @delim

      -- what is the position where the delimiter is located
      declare @pos int
      set @pos = charIndex(@delim, @input, 1)

      while (@pos > 0) begin
        if (@pos > 1) 
          insert into @output (theString)
          select left(@input, @pos-1)

        set @input = right(@input, len(@input)-@pos)
        set @pos = charIndex(@delim, @input, 1)
        end

      return
      end
    GO