Tips to improve SQL Server database design and performance

Best performance is the main concern to develop a successful application. Like a coin database is the tail side (back-end) of an application. A good database design provides best performance during data manipulation which results into the best performance of an application. Continue reading “Tips to improve SQL Server database design and performance”

Advertisements

Securing WCF Service with Self Signed Certificates programmatically

I’ve spent some time to deal with WCF securing with certificates and came to a solution that I want to share.

As you probably know, WCF supports certificate authentication and it’s not so hard to set up. However you will need to install certificates on both the service machine and the caller machine. This can be a problem if you want to host the service on a shared hosting environment for example. Even if the service is hosted on a machine in your network you will still need some permissions to be given to the service application pool user in order to access the certificate private key.So with the help of this blog post I found a way to create Self Signed certificate using some windows native methods.

using System;
using System.Runtime.InteropServices;

namespace Certificate.Native
{
    internal static class NativeMethods
    {
        [DllImport("kernel32.dll", SetLastError = true, ExactSpelling = true)]
        [return: MarshalAs(UnmanagedType.Bool)]
        public static extern bool FileTimeToSystemTime(
            [In] ref long fileTime,
            out SystemTime systemTime);

        [DllImport("AdvApi32.dll", SetLastError = true, ExactSpelling = true)]
        [return: MarshalAs(UnmanagedType.Bool)]
        public static extern bool CryptAcquireContextW(
            out IntPtr providerContext,
            [MarshalAs(UnmanagedType.LPWStr)] string container,
            [MarshalAs(UnmanagedType.LPWStr)] string provider,
            int providerType,
            int flags);

        [DllImport("AdvApi32.dll", SetLastError = true, ExactSpelling = true)]
        [return: MarshalAs(UnmanagedType.Bool)]
        public static extern bool CryptReleaseContext(
            IntPtr providerContext,
            int flags);

        [DllImport("AdvApi32.dll", SetLastError = true, ExactSpelling = true)]
        [return: MarshalAs(UnmanagedType.Bool)]
        public static extern bool CryptGenKey(
            IntPtr providerContext,
            int algorithmId,
            int flags,
            out IntPtr cryptKeyHandle);

        [DllImport("AdvApi32.dll", SetLastError = true, ExactSpelling = true)]
        [return: MarshalAs(UnmanagedType.Bool)]
        public static extern bool CryptDestroyKey(
            IntPtr cryptKeyHandle);

        [DllImport("Crypt32.dll", SetLastError = true, ExactSpelling = true)]
        public static extern IntPtr CertCreateSelfSignCertificate(
            IntPtr providerHandle,
            [In] ref CryptoApiBlob subjectIssuerBlob,
            int flags,
            [In] ref CryptKeyProviderInformation keyProviderInformation,
            IntPtr signatureAlgorithm,
            [In] ref SystemTime startTime,
            [In] ref SystemTime endTime,
            IntPtr extensions);

        [DllImport("Crypt32.dll", SetLastError = true, ExactSpelling = true)]
        [return: MarshalAs(UnmanagedType.Bool)]
        public static extern bool CertFreeCertificateContext(
            IntPtr certificateContext);
    }
}

I have created CertificateSerializer to serialize the certificate to base64string:

using System;
using System.IO;
using System.Security.Cryptography.X509Certificates;
namespace Certificate
{
    public class CertificateSerializer
    {
        public X509Certificate2 Deserialize(string certificateString)
        {
            byte[] numArray = Convert.FromBase64String(certificateString);
            string tempFileName = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
            try
            {
                File.WriteAllBytes(tempFileName, numArray);
                X509Certificate2 certificate = new X509Certificate2(tempFileName, default(string), X509KeyStorageFlags.Exportable);
                return certificate;
            }
            finally
            {
                File.Delete(tempFileName);
            }
        }

        public string Serialize(X509Certificate2 certificate)
        {
            byte[] numArray = certificate.Export(X509ContentType.Pfx);
            string base64String = Convert.ToBase64String(numArray);

            return base64String;
        }
    }
}

In the configuration section we can store the serialized certificate and the Thumbprints and Subjects of the trusted certificates, so that we can give the service the information about the trusted parties. Here’s an example of the custom section content:

    <configSections>
        <section name="certificateSection" type="Certificate.Configuration.CertificatesSection, Certificate"/>
    </configSections>
    <certificateSection certificate="MIIExgIBAzCCBIYGCSqGSIb3DQEHAaCCBHcEggRzMIIEbzCCApgGCSqGSIb3DQEHAaCCAokEggKFMIICgTCCAn0GCyqGSIb3DQEMCgECoIIBjjCCAYowHAYKKoZIhvcNAQwBAzAOBAgbm3IPSqU0BQICB9AEggFojvCBmeSJ6n4IlKxgSv1XgIB5LaD7tb06f/yTLsZRK+4rnqCaesmYFFHP889JTySdqPPyE6fNrpFXTcvcRC6lQQglLnxbRZQotPHvDv4MEzEdI67zkkfM7RsxxXRUQE+ex5H+oQxjScvVRWlKa0KXLk7DOa+Ijz/epLFCum2CE2aUE/AOdi8GCYf7D0yMa472/buQRX1qWX5MYuH+sZI1py/unS8R5R4cytRr8dKJHmn3YtLuhEwQOuXiQ/mUK5PKj+xYp6b8ssVIXQjuLpXZnnT7i/KdZipxmTCf+OtXbAysBw2VaQ9+NmR8cufy8nUb/KgSNfcE3hTHTxIaBnhddhuHxvfR5oYIAzPK3NTq/S1qCEqxDJnBFapdnRcKfHEAlDwIB/KZyHgKdVBiu16pB9e+bxl840CW6vI/tILBbpww3rjvzKKQYZZ6uPu1oNLS2TeX7JsBJE3p0HJE2DPFLfmXLPVPSkHBMYHbMBMGCSqGSIb3DQEJFTEGBAQBAAAAMFcGCSqGSIb3DQEJFDFKHkgAZgAzADEAMwBlADQAYQA4AC0AMAA2ADMANwAtADQANAA1ADAALQBiAGMANAA5AC0AMwA2AGEAMABkAGIAOQAxADkAOABhAGQwawYJKwYBBAGCNxEBMV4eXABNAGkAYwByAG8AcwBvAGYAdAAgAEUAbgBoAGEAbgBjAGUAZAAgAEMAcgB5AHAAdABvAGcAcgBhAHAAaABpAGMAIABQAHIAbwB2AGkAZABlAHIAIAB2ADEALgAwMIIBzwYJKoZIhvcNAQcGoIIBwDCCAbwCAQAwggG1BgkqhkiG9w0BBwEwHAYKKoZIhvcNAQwBBjAOBAjWmEM3BmtPWwICB9CAggGIIxS2KaegZ8TDfdq1AP55giyOzgLOvd1LYA5M1QwRmYcM4IiJe5Z6yB6usrnMa/oAJ6suBw72UTO8lTGc/AXWtbrJg6KM0CuyI7lKdoShn36FRx35djx5plXpDxVrZtR2MbOxgSdUNyUCiuRWe/FUzpwE93IWQnfIleeziH1YXuZdvxy/vTLKT2VngeZh3BjyG25n7Fj44xgy7CQM/g/q+TgHBJjnY9qD36kPdaWxkxytadpJH3GgnKLjoQCvHhFN6NEVhErnvzZo63jPZIDWHxr7EYGkbVTzwtPwlocTDgm75gS/IwCMNdAxHP9ofMM4H+2g/UV88R4ABgUoP139Drz5LrfHFsnvPx3/twygMX6lUccnwyKZTVcphjADHU6FVsm2/xJ/nqxCkiUt7ciz150FqGxJ+vxg5zo533eHjViwdDBHTMIopyypOY69xNfN1VGPMKxfc/d5z6ayKKpi9lXQMIUumoz5Xqjnq4dyschqoUbGNW1LB+0Y3BNHxeXyGlYsTsr9nYowNzAfMAcGBSsOAwIaBBRgiHbVQmQbvNqXli2R3sBoa6AirAQUeAWzhwSRejw9yMIGB2GgBY76bbM=">
        <trustedCertificates>
            <certificateInfo thumbprint="64123DFA95F03AFB818EC61C874241B62E2A4886" subject="ServiceCertificate"/>
        </trustedCertificates>
    </certificateSection>

I have created a small windows application for generating certificates and getting it’s serialized value and thumbprint:

Now we will create a custom service behavior extension to take care of the service credentials:

using System;
using System.ServiceModel.Configuration;

namespace Certificate.Extensions
{
    public class CertificateExtensionBehavior : BehaviorExtensionElement
    {
        public override Type BehaviorType
        {
            get { return typeof(CertificateServiceCredentials); }
        }

        protected override object CreateBehavior()
        {
            return new CertificateServiceCredentials();
        }
    }
}

Here is the CertificateServiceCredentials class which inherits from ServiceCredentials:

using System.Configuration;
using System.Configuration;
using System.Linq;
using System.ServiceModel.Description;
using Certificate.Configuration;

namespace Certificate.Extensions
{
    public class CertificateServiceCredentials : ServiceCredentials
    {
        public CertificateServiceCredentials()
        {
            //get the information from our custom configuration section
            CertificatesSection certificateSection = (CertificatesSection)ConfigurationManager.GetSection("certificateSection");
            var trustedList = certificateSection.TrustedCertificates.Cast<TrustedCertificateInfo>().Select(x => x.Thumbprint);

            this.ServiceCertificate.Certificate = certificateSection.Certificate;
            //we use custom validation mode to check the response
            this.ClientCertificate.Authentication.CertificateValidationMode = System.ServiceModel.Security.X509CertificateValidationMode.Custom;
            //and custom validator to validate if the response's certificate thumbprint is in the trusted thumbprints
            this.ClientCertificate.Authentication.CustomCertificateValidator =
                new CertificateValidator(trustedList);
        }

        protected override ServiceCredentials CloneCore()
        {
            return new CertificateServiceCredentials();
        }
    }
}

We use a custom certificate validation mode with Certificate validator who checks if the certificate in the response is in the current certificate trusted list. We get the certificate and the trusted list from our custom configuration section. And here’s the CertificateValidator:

using System;
using System;
using System.Collections.Generic;
using System.IdentityModel.Selectors;
using System.Linq;
using System.Security.Cryptography.X509Certificates;
using System.Security;

namespace Certificate
{
    public class CertificateValidator : X509CertificateValidator
    {
        private readonly IEnumerable<string> trustedThumbprints;

        public CertificateValidator(IEnumerable<string> thumbprints)
        {
            this.trustedThumbprints = thumbprints;
        }

        public override void Validate(X509Certificate2 certificate)
        {
            //check if there is a certificate in the request
            if (certificate == null)
            {
                throw new SecurityException("Missing certificate");
            }
            //check if the certificate thumbprint is in the list of the trusted ones
            if (!trustedThumbprints.Any(thumbprint => thumbprint.Equals(certificate.Thumbprint)))
            {
                throw new SecurityException("The provided certificate is not trusted!");
            }
        }
    }
}

So after that we need to set the service to use this custom extension, so the web.config of the service should look like this:

<?xml version="1.0"?>
<configuration>
  <configSections>
    <section name="certificateSection" type="Certificate.Configuration.CertificatesSection, Certificate"/>
  </configSections>
  <certificateSection certificate="MIIExgIBAzCCBIYGCSqGSIb3DQEHAaCCBHcEggRzMIIEbzCCApgGCSqGSIb3DQEHAaCCAokEggKFMIICgTCCAn0GCyqGSIb3DQEMCgECoIIBjjCCAYowHAYKKoZIhvcNAQwBAzAOBAiqwSbxN4Q2IQICB9AEggFoaa40+yIrF2Wb3/L4rYE7UpzrhI5S2O8wx72gj41tudES+QiM8DGxC1YWaHx4+THXFkQs62A6eDTKYOZUc8oMUFyVbY70Joq8un6keunCz4xP26MB2vygpx1/ASA6CTdeN3r9JEYss7DGaFnvUJKPH44oyBagaecT3fo0CA+Qa7vfcrlrhcyyovVs5lfJNUm13IF8/bNMCcOdgUnjX/tlay53YDulZSD0kP3apd60zzBAtIr2GD/h3NjiIcjSauDUf7bdvEV0LHAC78mRB/6nUaYiwZhAphky8ufR3dMzZGt5bglbEb8WkEw4bh/qrUxofA5uDmRgjnusAVtOcm0BUvK458bzsyKaRwuw8wSK+Srii5ZYjE5DSTc3msu5jCKZ5pC03w8tdeXmc5Xqq5TziKpDXW1bCa9D/O8mRnz+IsRa1FirDV/Spp37wyucLJkluKHZTpOeTWXRGy1/8Ys5kDxeXAamJxSLMYHbMBMGCSqGSIb3DQEJFTEGBAQBAAAAMFcGCSqGSIb3DQEJFDFKHkgAZAAzADAAMwBkADQAZgA2AC0AYQBmADAANQAtADQANwBjAGUALQBiAGIAZgBjAC0AMwBhAGMANQBlADgAYQBiAGUAOAA3ADcwawYJKwYBBAGCNxEBMV4eXABNAGkAYwByAG8AcwBvAGYAdAAgAEUAbgBoAGEAbgBjAGUAZAAgAEMAcgB5AHAAdABvAGcAcgBhAHAAaABpAGMAIABQAHIAbwB2AGkAZABlAHIAIAB2ADEALgAwMIIBzwYJKoZIhvcNAQcGoIIBwDCCAbwCAQAwggG1BgkqhkiG9w0BBwEwHAYKKoZIhvcNAQwBBjAOBAgatfFYOA+7qAICB9CAggGIOL+SmdG5n6oummdHrr7u0LH7+VwF3rICFqQTncXX9iVTND6DXJArJfFsYGs1fwq4mzTxmrpBArsf0pCht37x5m7m9k/JL/LeXWlh5re+tZptnEl/l/45AUvN3/fMzoaG4rD5keA1POOoir9fVTiiJjvPfIYvriI8siMwx13fyuFYNZlF+T1pkR6WQbRKTYS49nSGxhIgsoUkxXkGm64CRgXRriHqhopDqUmzCgHhE68jjt78Ff9iYl/1KYBvpJfgBTnvV0dNcXcHhmOkOLqHA6ONBFeARH6ous1i2AUoXfTVoFptTb0eSQTrZkravx2uJrSSuMtPP2qOkGkVQNE2TsJdyFVEKwhXuVyhpuDFky56Q73RDzQCfFEhZHfmleUCaZSVJlUXY86b6/Qk4ebzmGyje7+7z29PARHJBKWHJi/759fKmpTMO27gYor+ylFhqz21crjX7uae0jLKg59CjdSgJocpZ5jOK+B4sWqFuEYUMpcUcN3pZ2jkFMqQcWzOinegbwKeMzgwNzAfMAcGBSsOAwIaBBS9S/16xb6qbDTK5fY6EyjfSc/nvgQU1/tVtjNCPOGmYwOUDNupoL7hlpI=">
    <trustedCertificates>
      <certificateInfo thumbprint="625C675C8C7FF2A4041573116211367DABA71969" subject="CallerCertificate"/>
    </trustedCertificates>
  </certificateSection>
  <system.web>
    <compilation debug="true" targetFramework="4.0" />
  </system.web>
  <system.serviceModel>
    <behaviors>
      <serviceBehaviors>
        <behavior>
          <serviceMetadata httpGetEnabled="true" />
          <serviceDebug includeExceptionDetailInFaults="true" />
          <certificateExtension />
        </behavior>
      </serviceBehaviors>
    </behaviors>
    <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
    <services>
      <service name="Service.Service">
        <endpoint name="myService"
                  address=""
                  binding="wsDualHttpBinding"
                  contract="Service.IService"
                  bindingConfiguration="certificateBinding"/>
      </service>
    </services>
    <bindings>
      <wsDualHttpBinding>
        <binding name="certificateBinding">
          <security mode="Message">
            <message clientCredentialType="Certificate" />
          </security>
        </binding>
      </wsDualHttpBinding>
    </bindings>
    <extensions>
      <behaviorExtensions>
        <add name="certificateExtension" type="Certificate.Extensions.CertificateExtensionBehavior, Certificate"/>
      </behaviorExtensions>
    </extensions>
  </system.serviceModel>
  <system.webServer>
    <modules runAllManagedModulesForAllRequests="true"/>
  </system.webServer>
</configuration>

And the app.config of the caller should look like this:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <configSections>
        <section name="certificateSection" type="Certificate.Configuration.CertificatesSection, Certificate"/>
    </configSections>
    <certificateSection certificate="MIIExgIBAzCCBIYGCSqGSIb3DQEHAaCCBHcEggRzMIIEbzCCApgGCSqGSIb3DQEHAaCCAokEggKFMIICgTCCAn0GCyqGSIb3DQEMCgECoIIBjjCCAYowHAYKKoZIhvcNAQwBAzAOBAgbm3IPSqU0BQICB9AEggFojvCBmeSJ6n4IlKxgSv1XgIB5LaD7tb06f/yTLsZRK+4rnqCaesmYFFHP889JTySdqPPyE6fNrpFXTcvcRC6lQQglLnxbRZQotPHvDv4MEzEdI67zkkfM7RsxxXRUQE+ex5H+oQxjScvVRWlKa0KXLk7DOa+Ijz/epLFCum2CE2aUE/AOdi8GCYf7D0yMa472/buQRX1qWX5MYuH+sZI1py/unS8R5R4cytRr8dKJHmn3YtLuhEwQOuXiQ/mUK5PKj+xYp6b8ssVIXQjuLpXZnnT7i/KdZipxmTCf+OtXbAysBw2VaQ9+NmR8cufy8nUb/KgSNfcE3hTHTxIaBnhddhuHxvfR5oYIAzPK3NTq/S1qCEqxDJnBFapdnRcKfHEAlDwIB/KZyHgKdVBiu16pB9e+bxl840CW6vI/tILBbpww3rjvzKKQYZZ6uPu1oNLS2TeX7JsBJE3p0HJE2DPFLfmXLPVPSkHBMYHbMBMGCSqGSIb3DQEJFTEGBAQBAAAAMFcGCSqGSIb3DQEJFDFKHkgAZgAzADEAMwBlADQAYQA4AC0AMAA2ADMANwAtADQANAA1ADAALQBiAGMANAA5AC0AMwA2AGEAMABkAGIAOQAxADkAOABhAGQwawYJKwYBBAGCNxEBMV4eXABNAGkAYwByAG8AcwBvAGYAdAAgAEUAbgBoAGEAbgBjAGUAZAAgAEMAcgB5AHAAdABvAGcAcgBhAHAAaABpAGMAIABQAHIAbwB2AGkAZABlAHIAIAB2ADEALgAwMIIBzwYJKoZIhvcNAQcGoIIBwDCCAbwCAQAwggG1BgkqhkiG9w0BBwEwHAYKKoZIhvcNAQwBBjAOBAjWmEM3BmtPWwICB9CAggGIIxS2KaegZ8TDfdq1AP55giyOzgLOvd1LYA5M1QwRmYcM4IiJe5Z6yB6usrnMa/oAJ6suBw72UTO8lTGc/AXWtbrJg6KM0CuyI7lKdoShn36FRx35djx5plXpDxVrZtR2MbOxgSdUNyUCiuRWe/FUzpwE93IWQnfIleeziH1YXuZdvxy/vTLKT2VngeZh3BjyG25n7Fj44xgy7CQM/g/q+TgHBJjnY9qD36kPdaWxkxytadpJH3GgnKLjoQCvHhFN6NEVhErnvzZo63jPZIDWHxr7EYGkbVTzwtPwlocTDgm75gS/IwCMNdAxHP9ofMM4H+2g/UV88R4ABgUoP139Drz5LrfHFsnvPx3/twygMX6lUccnwyKZTVcphjADHU6FVsm2/xJ/nqxCkiUt7ciz150FqGxJ+vxg5zo533eHjViwdDBHTMIopyypOY69xNfN1VGPMKxfc/d5z6ayKKpi9lXQMIUumoz5Xqjnq4dyschqoUbGNW1LB+0Y3BNHxeXyGlYsTsr9nYowNzAfMAcGBSsOAwIaBBRgiHbVQmQbvNqXli2R3sBoa6AirAQUeAWzhwSRejw9yMIGB2GgBY76bbM=">
        <trustedCertificates>
            <certificateInfo thumbprint="64123DFA95F03AFB818EC61C874241B62E2A4886" subject="ServiceCertificate"/>
        </trustedCertificates>
    </certificateSection>
    <system.serviceModel>
        <bindings>
            <wsDualHttpBinding>
                <binding name="certificatesBinfing">
                    <security mode="Message">
                        <message clientCredentialType="Certificate"/>
                    </security>
                </binding>
            </wsDualHttpBinding>
        </bindings>
        <client>
            <endpoint address="http://localhost:9986/Service.svc" binding="wsDualHttpBinding"
                bindingConfiguration="certificatesBinfing" contract="IService"
                name="BasicHttpBinding_IService">
            </endpoint>
        </client>
    </system.serviceModel>
</configuration>

I have added a small WCF Extensions helper class from here.

using System;
using System.ServiceModel;

namespace Caller.Proxy
{
    public static class WcfExtensions
    {
        public static void Using<T>(this T client, Action<T> work)
            where T : ICommunicationObject
        {
            try
            {
                work(client);
                client.Close();
            }
            catch (CommunicationException)
            {
                client.Abort();
            }
            catch (TimeoutException)
            {
                client.Abort();
            }
            catch (Exception)
            {
                client.Abort();
                throw;
            }
        }
    }
}

Now the only thing we need to do is to call the service from the caller:

using System;
using System.Configuration;
using System.Linq;
using System.ServiceModel;
using Caller.Proxy;
using Certificate;
using Certificate.Configuration;

namespace Caller
{
    class Program
    {
        static void Main(string[] args)
        {
            new ServiceClient().Using(channel =>
            {
                //get the information from our custom configuration section
                CertificatesSection certificateSection = (CertificatesSection)ConfigurationManager.GetSection("certificateSection");
                var trustedList = certificateSection.TrustedCertificates.Cast().ToList();

                var endpointAddress = channel.Endpoint.Address.Uri;

                //get the first trusted certification. We assume that you will call only one service, so we will have only one item in the TrustedCertificates
                //this can be improved to select which service do you want to call an what is the response thumbprint that we expect
                string trustedSubject = trustedList.FirstOrDefault().Subject;

                //we create a dns identity from the trusted object to be able to authenticate with the service
                //
                //    
                //
                var identity = EndpointIdentity.CreateDnsIdentity(trustedSubject);

                channel.Endpoint.Address = new EndpointAddress(endpointAddress, identity);

                channel.ClientCredentials.ClientCertificate.Certificate = certificateSection.Certificate;
                //set custom validation mode
                channel.ClientCredentials.ServiceCertificate.Authentication.CertificateValidationMode = System.ServiceModel.Security.X509CertificateValidationMode.Custom;
                //trust results only from the certificate in the trusted list
                channel.ClientCredentials.ServiceCertificate.Authentication.CustomCertificateValidator = new CertificateValidator(trustedList.Select(x => x.Thumbprint));

                var data = channel.GetData(1);
                Console.WriteLine(data);
            });
        }
    }
}

And that’s it. We can create new certificate with the generator, set the serialized value in the configuration file of one of the sides and add the certificate subject and thumbprint in the other side’s trusted certificates and vice versa.

If you host the service and the site on IIS you need to set IIS Application Pool configuration (Application Pools > Advanced
Settings) to load the user profile for the application pool identity
user.

Otherwise the user may not be able to load the certificate.

 

Generate Create Statements for all indexes in a table

SELECT ' CREATE ' +
       CASE 
            WHEN I.is_unique = 1 THEN ' UNIQUE '
            ELSE ''
       END +
       I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
       I.name + ' ON ' +
       SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
       KeyColumns + ' )  ' +
       ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
       ISNULL(' WHERE  ' + I.filter_definition, '') + ' WITH ( ' +
       CASE 
            WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
            ELSE ' PAD_INDEX = OFF '
       END + ',' +
       'FILLFACTOR = ' + CONVERT(
           CHAR(5),
           CASE 
                WHEN I.fill_factor = 0 THEN 100
                ELSE I.fill_factor
           END
       ) + ',' +
       -- default value 
       'SORT_IN_TEMPDB = OFF ' + ',' +
       CASE 
            WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
            ELSE ' IGNORE_DUP_KEY = OFF '
       END + ',' +
       CASE 
            WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
            ELSE ' STATISTICS_NORECOMPUTE = ON '
       END + ',' +
       ' ONLINE = OFF ' + ',' +
       CASE 
            WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
            ELSE ' ALLOW_ROW_LOCKS = OFF '
       END + ',' +
       CASE 
            WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
            ELSE ' ALLOW_PAGE_LOCKS = OFF '
       END + ' ) ON [' +
       DS.name + ' ] ' +  CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
FROM   sys.indexes I
       JOIN sys.tables T
            ON  T.object_id = I.object_id
       JOIN sys.sysindexes SI
            ON  I.object_id = SI.id
            AND I.index_id = SI.indid
       JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name + CASE 
                                                                       WHEN MAX(CONVERT(INT, IC1.is_descending_key)) 
                                                                            = 1 THEN 
                                                                            ' DESC '
                                                                       ELSE 
                                                                            ' ASC '
                                                                  END
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column = 
                                                          0
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id
                                          ORDER BY
                                                 MAX(IC1.key_ordinal) 
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) KeyColumns
                           FROM   sys.index_columns IC2 
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp3
            )tmp4
            ON  I.object_id = tmp4.object_id
            AND I.Index_id = tmp4.index_id
       JOIN sys.stats ST
            ON  ST.object_id = I.object_id
            AND ST.stats_id = I.index_id
       JOIN sys.data_spaces DS
            ON  I.data_space_id = DS.data_space_id
       JOIN sys.filegroups FG
            ON  I.data_space_id = FG.data_space_id
       LEFT JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column = 
                                                          1
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id 
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) IncludedColumns
                           FROM   sys.index_columns IC2 
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp1
                WHERE  IncludedColumns IS NOT NULL
            ) tmp2
            ON  tmp2.object_id = I.object_id
            AND tmp2.index_id = I.index_id
WHERE  I.is_primary_key = 0
       AND I.is_unique_constraint = 0
           AND I.Object_id = object_id('tblERPInvoiceData') --Comment for all tables
           --AND I.name = 'IX_Address_PostalCode' --comment for all indexes

 
There is also one more on SQL Server Central:

https://www.sqlservercentral.com/Forums/Topic401784-562-3.aspx#bm1171799

Kerberos authentication and delegation: ServicePrincipalNames

My Hosting Blog

NOTE: while I’m still keeping the current posts live as they still seem to help, currently my focus has changed and new activity moved to the new site iternia.be

SPN’s

One of the errors that often reoccur when deploying a service is the Kerberos authentication failing for some reason when another system depends on your service. Depending users or services try to log on to your service but are not allowed to access it. This is not a problem with the enduser but with the rights of the service account on which the service itself is running. The service account doesn’t have the right to delegate access or impersonate the enduser. About 9 times out of 10 this is caused by inproper Kerberos rights due to a faulty SPN (or ServicePrincipalName) configuration and sometimes due to the delegation settings on the service account.

First lets take a look at how…

View original post 1,163 more words