• intekhab.alam (5/15/2009)


    Thanks for response.

    Here are the details abt my env...

    I am migrating db sql2k -->sql2k5 where sql2k5 is running on 2 node cluster. and to balance the node separating the db across two instance (active\active mode) running on same cluster nodes..

    Old sql2k instance were using 1433 node & to avoid code changes at app side to SQL to listen same port for both instances running on cluster.

    Cluster servername - serverN1\serverN2

    1st instance name - sqlvirtualA\instance1 - Port 1433,1434

    2st instance name - sqlvirtualB\instance2 - Port 1433,1435

    intekhab.alam (5/15/2009)


    I got the error message for SPM/kerberos authentication as you mentioned in your reply too.

    sql error log say ----

    "The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies."

    This isn't necessarily a problem. Because you are running your services under an AD account, it won't have the required privileges to create SPNs by default. It is better to create your SPNs manually anyway. To enable Kerberos in a cluster, you will need to enable "Register in DNS" and "Enable Kerberos" in your Network Name resource for each cluster group. This will require an cluster group outage to take effect. When it does, a new AD Computer account will be created in your CN=Computers folder. Move this to the same organisational unit as your serverN1 and serverN2 AD Computer accounts.

    Then create your SPN as required, eg

    setspn -A MSSQLSvc/sqlvirtualA:1433 [service account]

    setspn -A MSSQLSvc/sqlvirtualA.[your ad domain]:1433 [service account]

    Do that for both instances. You will also need to enable Delegation on the Kerberos tab of the that is running the SQL Database Engine. Setting it to Allow delegation for any Kerberos service is the easiest, but you can lock it down further if you wish.

    intekhab.alam (5/15/2009)


    based on this error message NTLM authentication scheme is used once kerberos failed. I queried below statement and result is "NTLM"

    "select auth_scheme from sys.dm_exec_connections where session_id=111"

    Expanding on that a little more nicely is

    select c.auth_scheme, s.login_name

    from sys.dm_exec_connections as c, sys.dm_exec_sessions as s

    where c.session_id = s.session_id

    intekhab.alam (5/15/2009)


    -- SQL instance is coming up properly & and I can make connection both the instances remotely using similar port using query analyser. also connection from app server\clinet have no issue using below connection string.

    sqlvirtualA,1433

    sqlvirtualA,1434

    sqlvirtualB,1433

    sqlvirtualB,1435

    - app uses dns alias to connect to SQL instances and have no issues as well.

    dns alias - sql-db1.fqdn mapped to sqlvirtualA

    dns alias - sql-db2.fqdn mapped to sqlvirtualB

    My biggest concern\question..

    how this will behave going forward?

    what idoes MS say for such cinfiguration? and des it a sound proof or suggested seting which can be implemnedted on critical Prod env?

    Are you\anone aware about such setting already implement on any env and how its working?

    again.. you reponse will be much appreciated. Awaiting your response.

    Thanks...

    If you are already using dns aliases to support your application (which is a good thing), then why do you want to try and make life a whole heap more complicated by trying to do what you're describing here? It is much simpler, and supported, to simply set each instance to a unique port. That they are in separate cluster groups gives them separate IP addresses. From memory, I don't think the SQL Network Configuration Utility provides an IP address option for your virtual (cluster group) IPs.

    --
    Andrew Hatfield