Category: SQL Server

  • Log Shipping Requirements

    Log Shipping Requirements

    Log shipping has the following requirements:
    * SQL Server 2005 Standard, SQL Server 2005 Workgroup, SQL Server 2005 Enterprise Edition, or a later version, must be installed on all server instances involved in log shipping.
    * The servers involved in log shipping should have the same case-sensitivity settings.
    * The databases in a log shipping configuration must use the full recovery model or bulk-logged recovery model.

    Permissions
    You must be a sysadmin on each server instance to enable log shipping. The backup and restore directories in your log shipping configuration must follow these requirements.
    * For the backup job, read/write permissions to the backup directory are required on the following:
    o The SQL Server service account on the primary server instance.
    o The proxy account of the backup job. By default, this is the SQL Server Agent account on the primary server instance.
    * For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.
    * For the restore job, read/write permission to the copy directory are required by the following:
    o The SQL Server service account on the secondary server instance.
    o The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance.

  • “Cannot Generate SSPI Context” error message, more comments for SQL Server

    The Cannot generate SSPI context issue is described by http://support.microsoft.com/?id=811889 in general. In this article we will discuss one daunting case of “Cannot generate SSPI context” error message when failing to connect to SQL server.

    In most related cases, customers report this issue as “They are not able to connect to their local SQL Server, but once they connect to my network, they can’t connection to my local SQL Server”.

    Such issue is reported against MSDE and SQLExpress versions. But actually, it can happen with any version/edition of SQL Server, including SQL Server 2000 and SQL Server 2005 that support NT integrated authentication.

    The error message for the failed connection is
    [SNAC] “[SQL Native Client]SQL Network Interfaces: The Local Security Authority cannot be contacted.[SQL Native Client]Cannot generate SSPI context”

    [MDAC] “Cannot generate SSPI context”;

    Failed System.Data.SqlClient.SqlException: Cannot generate SSPI context

    It can happen when all of followings are true:
    (1) The hosting machine of SQL Server is connected to a network, including home network or dialup connection, but it is disconnected from its domain.
    (2) The OS of the hosting machine is Windows XP or 2000. Not windows 2003.
    (3) The connection is to a local SQL Server.
    (4) Connection configuration causes network library to choose TCP/IP provider.

    A scenario that meets all of (1) (2) and (3) looks like an extreme corner case. But the reality is that it is quite often if the hosting machine is a laptop computer.

    To avoid condition (1) by connecting to your corporate domain through VPN or disconnecting from network completely.

    From user’s perspective, however, in many cases, either connecting over VPN or disconnecting from network might prevent you from accessing some valuable resources, so I want to discuss solutions that do not depend on (1) first.

    In most cases, users do not explicitly require TCP/IP as the connection provider. For example connection strings in form of “.\”, “(local)\”, “\” are among them. We might wonder why network library chooses TCP/IP provider instead of Shared Memory provider, if the connection string is not prefixed with “tcp” and the server is local.

    Answer is that it can happen if the TCP/IP provider is in front of other providers in the client protocol order list, or/and the local server is not listening on Share Memory and Name Pipe.

    As described above, only TCP/IP provider has the issue; hence, configuring network library not to choose TCP/IP is a solution. To do that, first, on the server side, make sure your server is listening on Shared Memory or/and Named Pipe connection requests; then, on the client side, change the protocol order list such that Shared Memory and/or Named Pipe are in front of TCP/IP, or prefixing your connection strings with “lpc” or “np” to force Shared Memory or Named Pipe, or using alias that prefix Named Pipe in connection strings, whichever you feel most comfortable with. Note that certain SKUs of SQL Server have named pipe connection turned off by default.

    If You really want the TCP/IP connection, the option is to use TCP/IP loop-back address, i.e. “127.0.0.1”, as your . You can also add an entry into the /etc/host file as well.

    For example, if your connection string has form of “\” and is not prefixed with “tcp”, without modifying the connection string, you can configure an alias with alias name as \, protocol as TCP/IP, server as “127.0.0.1\” or “127.0.0.1,”.

    Remember that the “Cannot Generate SSPI context” problem described in this post only happens when connecting to a local server; thus, the “127.0.0.1” is applicable.

    Be aware that only TCP/IP provider can provides the benefits of Kerberos authentication as discussed in http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

    From the error message reported by SNAC ODBC/OLEDB, you can differentiated the issue described by this post from another case of “Cannot generate SSPI context”, in which the root cause is because, in Active Directory, the Service Principle Name (SPN) of SQL Server is registered for a domain account different from the SQL Server is actually running under.

    Make sure service account has enough permission in AD to register the SPN. You can check the below parameters for SPN as well.

    SET SPN-L Service Account. You can get more information for the SPN from this post.
    http://sqldbpool.com/2009/11/26/service-principle-name/

    Check that SPN is registered, if registered there shouldn’t be duplicate entry, should be match with the service account, Service account should have enough permission, PORT number is correct.

    For the connection failure…
    You can also check Resolving Connection failure article.

    Troubleshooting this in the AI era

    Years later, my approach to this error has a new step. I still run the deterministic checks first — DNS, port, clock skew, SPNs, the actual auth scheme — because those are facts, not guesses. Then I paste the output into an AI assistant and let it rank the likely causes. The script does the facts; the AI does the reasoning. Here is the script:

    <#
    .SYNOPSIS
        Troubleshoot "Cannot generate SSPI context" — SQL Server Kerberos checklist.
    .DESCRIPTION
        Run from the CLIENT machine. Checks DNS, port reachability, time skew,
        SPN registration, and the actual auth scheme of a live connection.
        Paste the full output into an AI assistant with the prompt printed at the end.
    .PARAMETER ServerInstance
        e.g. "SQL01" or "SQL01\INST1,1433"
    .PARAMETER ServiceAccount
        The domain account running SQL Server, e.g. "CONTOSO\sqlsvc".
        Needed for the SPN check.
    .EXAMPLE
        .\Test-SspiContext.ps1 -ServerInstance "SQL01" -ServiceAccount "CONTOSO\sqlsvc"
    #>
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)][string]$ServerInstance,
        [string]$ServiceAccount
    )
    $targetHost = ($ServerInstance -split '[\\,]')[0]
    Write-Host "=== SSPI / Kerberos diagnostics for $ServerInstance ===" -ForegroundColor Cyan
    # 1. DNS — Kerberos needs the *name*, not the IP, to match the SPN
    Write-Host "`n[1] DNS resolution"
    try {
        $dns = Resolve-DnsName $targetHost -ErrorAction Stop | Select-Object -First 1
        Write-Host "  OK: $targetHost -> $($dns.IPAddress)"
    } catch { Write-Host "  FAIL: cannot resolve $targetHost" -ForegroundColor Red }
    # 2. Port reachability
    Write-Host "`n[2] TCP 1433 reachability"
    $t = Test-NetConnection -ComputerName $targetHost -Port 1433 -WarningAction SilentlyContinue
    if ($t.TcpTestSucceeded) { Write-Host "  OK: port 1433 open" }
    else { Write-Host "  FAIL: port 1433 unreachable" -ForegroundColor Red }
    # 3. Time skew — Kerberos dies beyond ~5 minutes
    Write-Host "`n[3] Time skew (Kerberos tolerance: 5 min)"
    Write-Host "  Local time (UTC): $([DateTime]::UtcNow.ToString('u'))"
    Write-Host "  If the server clock differs by more than 5 minutes, Kerberos auth fails."
    # 4. SPN registration for the service account
    if ($ServiceAccount) {
        Write-Host "`n[4] SPNs registered for $ServiceAccount"
        $spns = setspn -L $ServiceAccount 2>&1
        $mssql = $spns | Select-String 'MSSQLSvc'
        if ($mssql) { $mssql | ForEach-Object { Write-Host "  $_" } }
        else { Write-Host "  FAIL: no MSSQLSvc SPN found — the classic SSPI cause" -ForegroundColor Red }
    } else {
        Write-Host "`n[4] SPN check skipped (pass -ServiceAccount to enable)"
    }
    # 5. Live connection: what auth scheme did we actually negotiate?
    Write-Host "`n[5] Live connection auth scheme"
    try {
        $cs = "Server=$ServerInstance;Integrated Security=SSPI;Connection Timeout=10;"
        $con = New-Object System.Data.SqlClient.SqlConnection($cs)
        $con.Open()
        $cmd = $con.CreateCommand()
        $cmd.CommandText = "SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID"
        $scheme = $cmd.ExecuteScalar()
        $con.Close()
        if ($scheme -eq 'KERBEROS') { Write-Host "  OK: connected via KERBEROS" -ForegroundColor Green }
        else { Write-Host "  WARN: connected via $scheme (not Kerberos) — delegation / double-hop will fail" -ForegroundColor Yellow }
    } catch {
        Write-Host "  FAIL: $($_.Exception.Message)" -ForegroundColor Red
    }
    Write-Host "`n=== Now paste everything above into your AI assistant with this prompt: ===" -ForegroundColor Cyan
    @'
    I'm getting "Cannot generate SSPI context" connecting to SQL Server from a domain-joined client.
    Below is my diagnostic output. Tell me the single most likely root cause and the exact fix,
    ordered by likelihood. Be specific — SPN commands, account names, exact syntax.
    '@

    Run it as .\Test-SspiContext.ps1 -ServerInstance “SQL01” -ServiceAccount “CONTOSO\sqlsvc”, then paste the full output into your AI assistant with the prompt the script prints. The script gathers the evidence; the AI ranks the suspects.

    I’m getting “Cannot generate SSPI context” connecting to SQL Server from a domain-joined client. Below is my diagnostic output. Tell me the single most likely root cause and the exact fix, ordered by likelihood. Be specific — SPN commands, account names, exact syntax.

  • How to take backup in Multiple files?

    Problem
    How to take backup in Multiple files (Or you can say how to split backup file in multiple backup files)?

    Solution

    Advantage:

    1. We can store backup on multiple drive if disk space is the issue

    2. Easy to transfer on network servers

    T-SQL for Backup

    BACKUP DATABASE [Northwind] TO

    DISK = ‘C:\Northwind_file1.bak’,

    DISK = ‘D:\Northwind_file2.bak’,

    DISK = ‘E:\Northwind_file3.bak’

    WITH INIT , NOUNLOAD , NAME = ‘Northwind backup’, NOSKIP , STATS = 10, NOFORMAT

    T-SQL for Restore

    RESTORE DATABASE [northwind] FROM

    DISK = N’C:\Northwind_file1.bak’,

    DISK = N’D:\Northwind_file2.bak’,

    DISK = N’E:\Northwind_file3.bak’ WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , REPLACE

  • SQL Server Windows Authentication (MIX Mode Authenticatio)

    SSPI (Security Support Provider Interface)

    SSPI allows an application to use various security models available on a computer or network without changing the interface to the security system.

    What is Kerberos?

    Kerberos is a network authentication protocol. It is designed to provide strong authentication for client/server applications by using secret-key cryptography. A free implementation of this protocol is available from the Massachusetts Institute of Technology. Kerberos is available in many commercial products as well.

    The Internet is an insecure place. Many of the protocols used in the Internet do not provide any security. Tools to “sniff” passwords off of the network are in common use by malicious hackers. Thus, applications which send an unencrypted password over the network are extremely vulnerable. Worse yet, other client/server applications rely on the client program to be “honest” about the identity of the user who is using it. Other applications rely on the client to restrict its activities to those which it is allowed to do, with no other enforcement by the server.

    Some sites attempt to use firewalls to solve their network security problems. Unfortunately, firewalls assume that “the bad guys” are on the outside, which is often a very bad assumption. Most of the really damaging incidents of computer crime are carried out by insiders. Firewalls also have a significant disadvantage in that they restrict how your users can use the Internet. (After all, firewalls are simply a less extreme example of the dictum that there is nothing more secure then a computer which is not connected to the network — and powered off!) In many places, these restrictions are simply unrealistic and unacceptable.

    Kerberos was created by MIT as a solution to these network security problems. The Kerberos protocol uses strong cryptography so that a client can prove its identity to a server (and vice versa) across an insecure network connection. After a client and server has used Kerberos to prove their identity, they can also encrypt all of their communications to assure privacy and data integrity as they go about their business.

    Kerberos is freely available from MIT, under copyright permissions very similar those used for the BSD operating system and the X Window System. MIT provides Kerberos in source form so that anyone who wishes to use it may look over the code for themselves and assure themselves that the code is trustworthy. In addition, for those who prefer to rely on a professionally supported product, Kerberos is available as a product from many different vendors.

    In summary, Kerberos is a solution to your network security problems. It provides the tools of authentication and strong cryptography over the network to help you secure your information systems across your entire enterprise. We hope you find Kerberos as useful as it has been to us. At MIT, Kerberos has been invaluable to our Information/Technology architecture.

    NTLM (NT LAN Manager)

    NTLM is a suite of authentication and session security protocols used in various Microsoft network protocol implementations and supported by the NTLM Security Support Provider (“NTLMSSP”). Originally used for authentication and negotiation of secure DCE/RPC, NTLM is also used throughout Microsoft’s systems as an integrated single sign-on mechanism. It is probably best recognized as part of the “Integrated Windows Authentication” stack for HTTP authentication; however, it is also used in Microsoft implementations of SMTP, POP3, IMAP (all part of Exchange), CIFS/SMB, Telnet, SIP, and possibly others.

    The NTLM Security Support Provider provides authentication, integrity, and confidentiality services within the Window Security Support Provider Interface (SSPI) framework. SSPI specifies a core set of security functionality that is implemented by supporting providers; the NTLMSSP is such a provider. The SSPI specifies, and the NTLMSSP implements, the following core operations:

    1. Authentication — NTLM provides a challenge-response authentication mechanism, in which clients are able to prove their identities without sending a password to the server.

    2. Signing — The NTLMSSP provides a means of applying a digital “signature” to a message. This ensures that the signed message has not been modified (either accidentally or intentionally) and that that signing party has knowledge of a shared secret. NTLM implements a symmetric signature scheme (Message Authentication Code, or MAC); that is, a valid signature can only be generated and verified by parties that possess the common shared key.

    3. Sealing — The NTLMSSP implements a symmetric-key encryption mechanism, which provides message confidentiality. In the case of NTLM, sealing also implies signing (a signed message is not necessarily sealed, but all sealed messages are signed).

    NTLM has been largely supplanted by Kerberos as the authentication protocol of choice for domain-based scenarios. However, Kerberos is a trusted-third-party scheme, and cannot be used in situations where no trusted third party exists; for example, member servers (servers that are not part of a domain), local accounts, and authentication to resources in an untrusted domain. In such scenarios, NTLM continues to be the primary authentication mechanism (and likely will be for a long time).

    How to make sure that you are using Kerberos authentication

    SQL Server 2005 supports Kerberos authentication indirectly through the Windows Security Support Provider Interface (SSPI) when you are using Windows integrated authentication instead of SQL authentication. However, SQL Server will only use Kerberos authentication under certain circumstances when SQL Server can use SSPI to negotiate the authentication protocol to use. If SQL Server cannot use Kerberos authentication, Windows will use NTLM authentication. For security reasons, we recommend that you use Kerberos authentication instead of NTLM authentication. Administrators and users should know how to make sure that they are using Kerberos authentication for remote connections.

    To use Kerberos authentication, you must make sure that all the following conditions are true:

    • Both the server and the client computers must be members of the same Windows domain or members of trusted domains.

    • The server’s service principal name (SPN) must be registered in the Active Directory service.

    • The instance of SQL Server 2005 must enable the TCP/IP protocol.

    • The client must connect to the instance of SQL Server 2005 by using the TCP/IP protocol. For example, you can put the TCP/IP protocol at the top of the client’s protocol order. Or you can add the prefix “tcp:” in the connection string to specify that the connection will use the TCP/IP protocol.

    After you connected to an instance of SQL Server 2005, run the following Transact-SQL statement in SQL Server Management Studio:

    select auth_scheme from sys.dm_exec_connections where session_id=@@spid

    If SQL Server is using Kerberos authentication, a character string that is listed as “KERBEROS” appears in the auth_scheme column in the result window.

  • Can not open user default database; login failed

    Troubleshooting Authentication Issues

    Problem:

    Can not open user default database; login failed

    Scenario:

    I have windows DBA group which has default database DB1 and has SYSADMIN rights on SQL Server, by some reasons the DB1 database is corrupted and user is unable to login.

    Solution:

    Step 1:

    Connect SQL Server using SQLCMD/OSQL as below and give the default database name as MASTER

    sqlcmd -E -d master

    Step 2: Now issue ALTER LOGIN statement and change the default database

    Use SP_DefaultDB procedure to change the default database

    sp_defaultdb (SQL Server 2000)

    Changes the default database for a login

    Syntax

    EXEC sp_defaultdb 'Victoria', 'pubs'

    (SQL Server 2005)

    ALTER LOGIN LOGINNAME WITH DEFAULT_DATABASE = MASTER