Wednesday, August 26, 2015

Start SQL Server Analysis Service in Console mode

C:\MSAS12.PROD\OLAP\bin>msmdsrv.exe -s C:\MSAS12.PROD\OLAP\Config -c
-s C:\MSAS12.PROD\OLAP\Config -c
Message: The flight recorder was started. (Source: MSOLAP$PROD, Type: 1, Category: 289, Event ID: 0x41210005)
Message: Service started. Microsoft SQL Server Analysis Services 64 Bit Enterprise (x64) SP1 12.0.4100.1. (Source: MSOLAP$PROD, Type: 1, Category: 289
, Event ID: 0x41210000)
Message: Software usage metrics started successfully. (Source: MSOLAP$PROD, Type: 1, Category: 289, Event ID: 0x41210030)

image

Posted by Luo Donghua at 10:52 PM 0 comments
Labels:

How to change the SSAS Port number

SSAS default instance or clustered instance (regardless named instance or not) default to 2383.

For SSAS named instance in standalone environment, default port is high port, and SQL Server Browser Service Listening on TCP 2382.

To change the port:

Edit file “<Instance Name>\OLAP\Config\msmdsrv.ini”, change the port number from “0” to desired value. (3034 in our case).

image

Don't forget to configure your firewall to allow incoming requests on TCP port 3034.

netsh advfirewall firewall add rule name="SQL Server Analysis Services (tcp-in) on 3034" dir=in action=allow protocol=TCP localport=3034 profile=domain

Restart the SSRS Service

image

C:\Users\administrator.DBAGLOBE>tasklist | findstr msmdsrv.exe
msmdsrv.exe                   1084 Services                   0     34,828 K

C:\Users\administrator.DBAGLOBE>netstat -nao | findstr 1084
  TCP    0.0.0.0:3034           0.0.0.0:0              LISTENING       1084
  TCP    [::]:3034              [::]:0                 LISTENING       1084

Connect to the SSAS using format “machinename:port” format, instead of “instancename:port” format.

image

Incorrect format:

image

Posted by Luo Donghua at 10:41 PM 0 comments
Labels:

Find out which port SQL Server services listening on

image

C:\>tasklist |findstr 2060
sqlbrowser.exe                2060 Services                   0     10,000 K

C:\>netstat -nao | findstr 2060
  TCP    0.0.0.0:2382           0.0.0.0:0              LISTENING       2060
  TCP    [::]:2382              [::]:0                 LISTENING       2060
  UDP    0.0.0.0:1434           *:*                                    2060
  UDP    [::]:1434              *:*                                    2060

C:\>tasklist | findstr 2456
sqlservr.exe                  2456 Services                   0    174,624 K

C:\>netstat -nao | findstr 2456
  TCP    0.0.0.0:3033           0.0.0.0:0              LISTENING       2456
  TCP    127.0.0.1:64254        0.0.0.0:0              LISTENING       2456
  TCP    [::]:3033              [::]:0                 LISTENING       2456
  TCP    [::1]:64254            [::]:0                 LISTENING       2456

C:\>tasklist | findstr 2508
SQLAGENT.EXE                  2508 Services                   0     20,732 K

C:\>netstat -nao | findstr 2508

C:\>tasklist | findstr 2156
fdlauncher.exe                2156 Services                   0      3,476 K

C:\>netstat -nao | findstr 2156

C:\>tasklist | findstr 1500
msmdsrv.exe                   1500 Services                   0     35,864 K

C:\>netstat -nao | findstr 1500
  TCP    0.0.0.0:64310          0.0.0.0:0              LISTENING       1500
  TCP    [::]:64310             [::]:0                 LISTENING       1500

Posted by Luo Donghua at 10:13 PM 0 comments
Labels:

Tuesday, August 18, 2015

Install MySQL Python Connector

[mysql@localhost repo]$ tar zxvf mysql-connector-python-commercial-2.0.4-py2.7.tar.gz
[mysql@localhost repo]$ sudo su -
[root@localhost repo]# cd /mysql/repo/mysql-connector-python-commercial-2.0.4-py2.7
[root@localhost mysql-connector-python-commercial-2.0.4-py2.7]#
[root@localhost mysql-connector-python-commercial-2.0.4-py2.7]# python setup.py install
running install
running build
copying mysql/connector/pooling.pyc -> build/lib/mysql/connector
copying mysql/connector/custom_types.pyc -> build/lib/mysql/connector
copying mysql/connector/fabric/connection.pyc -> build/lib/mysql/connector/fabric
copying mysql/connector/fabric/caching.pyc -> build/lib/mysql/connector/fabric
copying mysql/connector/fabric/balancing.pyc -> build/lib/mysql/connector/fabric
copying mysql/connector/fabric/__init__.pyc -> build/lib/mysql/connector/fabric
copying mysql/connector/catch23.pyc -> build/lib/mysql/connector
copying mysql/connector/connection.pyc -> build/lib/mysql/connector
copying mysql/connector/errorcode.pyc -> build/lib/mysql/connector
copying mysql/connector/cursor.pyc -> build/lib/mysql/connector
copying mysql/connector/conversion.pyc -> build/lib/mysql/connector
copying mysql/connector/authentication.pyc -> build/lib/mysql/connector
copying mysql/connector/locales/eng/client_error.pyc -> build/lib/mysql/connector/locales/eng
copying mysql/connector/locales/eng/__init__.pyc -> build/lib/mysql/connector/locales/eng
copying mysql/connector/locales/__init__.pyc -> build/lib/mysql/connector/locales
copying mysql/connector/errors.pyc -> build/lib/mysql/connector
copying mysql/connector/__init__.pyc -> build/lib/mysql/connector
copying mysql/connector/protocol.pyc -> build/lib/mysql/connector
copying mysql/connector/charsets.pyc -> build/lib/mysql/connector
copying mysql/connector/dbapi.pyc -> build/lib/mysql/connector
copying mysql/connector/utils.pyc -> build/lib/mysql/connector
copying mysql/connector/django/schema.pyc -> build/lib/mysql/connector/django
copying mysql/connector/django/introspection.pyc -> build/lib/mysql/connector/django
copying mysql/connector/django/client.pyc -> build/lib/mysql/connector/django
copying mysql/connector/django/base.pyc -> build/lib/mysql/connector/django
copying mysql/connector/django/compiler.pyc -> build/lib/mysql/connector/django
copying mysql/connector/django/validation.pyc -> build/lib/mysql/connector/django
copying mysql/connector/django/__init__.pyc -> build/lib/mysql/connector/django
copying mysql/connector/django/creation.pyc -> build/lib/mysql/connector/django
copying mysql/connector/constants.pyc -> build/lib/mysql/connector
copying mysql/connector/network.pyc -> build/lib/mysql/connector
copying mysql/connector/optionfiles.pyc -> build/lib/mysql/connector
copying mysql/connector/version.pyc -> build/lib/mysql/connector
copying mysql/__init__.pyc -> build/lib/mysql
running install_lib
running build_py
package init file 'mysql/__init__.py' not found (or not a regular file)
package init file 'mysql/connector/__init__.py' not found (or not a regular file)
package init file 'mysql/connector/locales/__init__.py' not found (or not a regular file)
package init file 'mysql/connector/locales/eng/__init__.py' not found (or not a regular file)
package init file 'mysql/__init__.py' not found (or not a regular file)
package init file 'mysql/connector/__init__.py' not found (or not a regular file)
package init file 'mysql/connector/locales/__init__.py' not found (or not a regular file)
package init file 'mysql/connector/locales/eng/__init__.py' not found (or not a regular file)
running install_egg_info
Removing /usr/lib/python2.7/site-packages/mysql_connector_python-2.0.4-py2.7.egg-info
Writing /usr/lib/python2.7/site-packages/mysql_connector_python-2.0.4-py2.7.egg-info

Sample Python Code for verifying the installation:

import mysql.connector

config = {
  'user': 'root',
  'password': 'complex_password',
  'host': 'localhost',
  'port': '5001',
  'database': 'appdb',
  'raise_on_warnings': True,
}

cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()

query = ("select * from t1")

cursor.execute(query)

for (id,last_updated) in cursor:
  print(str(id)+': '+str(last_updated))

cursor.close()
cnx.close()

[mysql@localhost repo]$ python test.py
1: 2015-08-14 23:01:49
2: 2015-08-14 23:25:42
3: 2015-08-14 23:27:17
4: 2015-08-14 23:29:01
5: 2015-08-14 23:30:00

Setup parameters:

[root@localhost mysql-connector-python-commercial-2.0.4-py2.7]# python setup.py --help
Common commands: (see '--help-commands' for more)

  setup.py build      will build the package underneath 'build/'
  setup.py install    will install the package

Global options:
  --verbose (-v)      run verbosely (default)
  --quiet (-q)        run quietly (turns verbosity off)
  --dry-run (-n)      don't actually do anything
  --help (-h)         show detailed help message
  --no-user-cfg       ignore pydistutils.cfg in your home directory
  --command-packages  list of packages that provide distutils commands

Information display options (just display information, ignore any commands)
  --help-commands     list all available commands
  --name              print package name
  --version (-V)      print package version
  --fullname          print <package name>-<version>
  --author            print the author's name
  --author-email      print the author's email address
  --maintainer        print the maintainer's name
  --maintainer-email  print the maintainer's email address
  --contact           print the maintainer's name if known, else the author's
  --contact-email     print the maintainer's email address if known, else the
                      author's
  --url               print the URL for this package
  --license           print the license of the package
  --licence           alias for --license
  --description       print the package description
  --long-description  print the long package description
  --platforms         print the list of platforms
  --classifiers       print the list of classifiers
  --keywords          print the list of keywords
  --provides          print the list of packages/modules provided
  --requires          print the list of packages/modules required
  --obsoletes         print the list of packages/modules made obsolete

usage: setup.py [global_opts] cmd1 [cmd1_opts] [cmd2 [cmd2_opts] ...]
   or: setup.py --help [cmd1 cmd2 ...]
   or: setup.py --help-commands
   or: setup.py cmd --help

Posted by Luo Donghua at 5:03 AM 0 comments
Labels:

Sunday, August 16, 2015

Skip MySQL transactions on Slave when GTID configured

Symptoms:

server2> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: repl_user
                  Master_Port: 5001
                Connect_Retry: 60
              Master_Log_File: bin-log.000012
          Read_Master_Log_Pos: 2255
               Relay_Log_File: relay-bin.000016
                Relay_Log_Pos: 1902
        Relay_Master_Log_File: bin-log.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1032
                   Last_Error: Worker 0 failed executing transaction 'e645f6c4-428d-11e5-bf83-08002702dadb:20' at master log bin-log.000012, end_log_pos 1939; Could not execute Delete_rows event on table appdb.a; Can't find record in 'a', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log bin-log.000012, end_log_pos 1939
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1696
              Relay_Log_Space: 2860
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1032
               Last_SQL_Error: Worker 0 failed executing transaction 'e645f6c4-428d-11e5-bf83-08002702dadb:20' at master log bin-log.000012, end_log_pos 1939; Could not execute Delete_rows event on table appdb.a; Can't find record in 'a', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log bin-log.000012, end_log_pos 1939
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: e645f6c4-428d-11e5-bf83-08002702dadb
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 150816 22:53:41
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: e645f6c4-428d-11e5-bf83-08002702dadb:5-21
            Executed_Gtid_Set: b8ac4b6b-428f-11e5-bf8f-08002702dadb:1-5,
e645f6c4-428d-11e5-bf83-08002702dadb:5-19
                Auto_Position: 0
1 row in set (0.00 sec)

Check the failed statements:

[mysql@localhost script]$ mysqlbinlog /mysql/server1/repl/bin-log.000012 --start-position=1939 --stop-position=1939
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150816 22:41:17 server id 1  end_log_pos 120 CRC32 0xa5f1c472  Start: binlog v 4, server v 5.6.26-enterprise-commercial-advanced-log created 150816 22:41:17 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
DaHQVQ8BAAAAdAAAAHgAAAABAAQANS42LjI2LWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAAANodBVEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAXLE
8aU=
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


[mysql@localhost script]$ mysqlbinlog /mysql/server1/repl/bin-log.000012 -v
SET @@SESSION.GTID_NEXT= 'e645f6c4-428d-11e5-bf83-08002702dadb:20'/*!*/;
# at 1744
#150816 22:53:41 server id 1  end_log_pos 1817 CRC32 0x96f22726         Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1439736821/*!*/;
BEGIN
/*!*/;
# at 1817
# at 1854
#150816 22:53:41 server id 1  end_log_pos 1899 CRC32 0x384ad943         Table_map: `appdb`.`a` mapped to number 71
# at 1899
#150816 22:53:41 server id 1  end_log_pos 1939 CRC32 0xb9074e0e         Delete_rows: table id 71 flags: STMT_END_F

BINLOG '
9aPQVRMBAAAALQAAAGsHAAAAAEcAAAAAAAEABWFwcGRiAAFhAAEDAAFD2Uo4
9aPQVSABAAAAKAAAAJMHAAAAAEcAAAAAAAEAAgAB//4AAAAADk4HuQ==
'/*!*/;
### DELETE FROM `appdb`.`a`
### WHERE
###   @1=0
# at 1939
#150816 22:53:41 server id 1  end_log_pos 1970 CRC32 0xf10570bc         Xid = 62
COMMIT/*!*/;
# at 1970
#150816 22:55:06 server id 1  end_log_pos 2018 CRC32 0x839b9136         GTID [commit=yes]

Fix the replication:

server2> stop slave;
Query OK, 0 rows affected (0.00 sec)

server2> set gtid_next='e645f6c4-428d-11e5-bf83-08002702dadb:20';
Query OK, 0 rows affected (0.00 sec)

server2> begin;commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

server2> set gtid_next="automatic";
Query OK, 0 rows affected (0.00 sec)

server2> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

server2> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: repl_user
                  Master_Port: 5001
                Connect_Retry: 60
              Master_Log_File: bin-log.000012
          Read_Master_Log_Pos: 2255
               Relay_Log_File: relay-bin.000017
                Relay_Log_Pos: 352
        Relay_Master_Log_File: bin-log.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2255
              Relay_Log_Space: 2860
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: e645f6c4-428d-11e5-bf83-08002702dadb
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: e645f6c4-428d-11e5-bf83-08002702dadb:5-21
            Executed_Gtid_Set: b8ac4b6b-428f-11e5-bf8f-08002702dadb:1-5,
e645f6c4-428d-11e5-bf83-08002702dadb:5-21
                Auto_Position: 0
1 row in set (0.00 sec)

 

Alternative method:

server2> stop slave;
Query OK, 0 rows affected (0.01 sec)

server2> CHANGE MASTER TO MASTER_HOST='localhost', MASTER_PORT=5001, MASTER_USER='repluser', MASTER_PASSWORD='compexpassword’, MASTER_LOG_FILE='bin-log.000012', MASTER_LOG_POS=2555;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

server2> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Reference: https://www.percona.com/blog/2013/03/26/repair-mysql-5-6-gtid-replication-by-injecting-empty-transactions/

Posted by Luo Donghua at 11:20 PM 0 comments
Labels:

Friday, August 14, 2015

Emulating wide area network delays

http://www.linuxfoundation.org/collaborate/workgroups/networking/netem

This is the simplest example, it just adds a fixed amount of delay to all packets going out of the local Ethernet.

# tc qdisc add dev eth0 root netem delay 100ms

Real Examples:

[root@localhost ~]# ifconfig -a
enp0s3: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.56.101  netmask 255.255.255.0  broadcast 192.168.56.255
        inet6 fe80::a00:27ff:fe02:dadb  prefixlen 64  scopeid 0x20<link>
        ether 08:00:27:02:da:db  txqueuelen 1000  (Ethernet)
        RX packets 836  bytes 75866 (74.0 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 652  bytes 74833 (73.0 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@localhost ~]# tc qdisc add dev enp0s3 root netem delay 100ms
[root@localhost ~]# tc qdisc show dev enp0s3
qdisc netem 8002: root refcnt 2 limit 1000 delay 100.0ms
[root@localhost ~]# tc qdisc delete dev enp0s3 root netem delay 100ms
[root@localhost ~]# tc qdisc show dev enp0s3
qdisc pfifo_fast 0: root refcnt 2 bands 3 priomap  1 2 2 2 1 2 0 0 1 1 1 1 1 1 1 1

The below screenshot of ping clearly demonstrates the latency added (100ms).

image

Posted by Luo Donghua at 9:38 PM 0 comments
Labels:

Thursday, August 6, 2015

Enable Read-Only Routing using T-SQL in SQL Server 2014 AlwaysOn Availability Groups

Symptom:

C:\Users\administrator.DBAGLOBE>sqlcmd -S vmmag02,2433 -d reportserver -K readonly -Q "select @@servername"

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : The target database ('reportserver') is in an availability group and currently does not allow read only connections. For more information about application intent, see SQL Server Books Online.

Enable Read-Only Routing using T-SQL in SQL Server 2014 AlwaysOn Availability Groups

select ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ar.primary_role_allow_connections_desc,
    ar.secondary_role_allow_connections_desc,
    ar.read_only_routing_url
from sys.availability_groups ag, sys.availability_replicas ar
where ag.group_id=ar.group_id
and ag.name='SSRS_AG'

image

image

ALTER AVAILABILITY GROUP SSRS_AG
MODIFY REPLICA ON 'VMMSQL01'
WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL='TCP://VMMSQL01.dbaglobe.com:1433'))

ALTER AVAILABILITY GROUP SSRS_AG
MODIFY REPLICA ON 'VMMSQL02'
WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL='TCP://VMMSQL02.dbaglobe.com:1433'))

ALTER AVAILABILITY GROUP SSRS_AG
MODIFY REPLICA ON 'VMMSQL01'
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST =('VMMSQL02')))

ALTER AVAILABILITY GROUP SSRS_AG
MODIFY REPLICA ON 'VMMSQL02'
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST =('VMMSQL01')))

image

image

image

image

 

Reference URL:

https://msdn.microsoft.com/en-us/library/hh710054.aspx

https://www.mssqltips.com/sqlservertip/2869/configure-sql-server-2012-alwayson-availability-groups-readonly-routing-using-tsql/

Posted by Luo Donghua at 3:17 PM 0 comments
Labels:

Wednesday, August 5, 2015

SQL Server AG listener creation with customized OU

Symptom:

USE [master]
GO
ALTER AVAILABILITY GROUP [TestDB1_AG]
ADD LISTENER N'VMMAG01' (
WITH IP
((N'10.0.2.201', N'255.255.255.0')
)
, PORT=1433);

Msg 19471, Level 16, State 0, Line 3
The WSFC cluster could not bring the Network Name resource with DNS name 'VMMAG01' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.

Msg 19476, Level 16, State 4, Line 3
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator.

How to fix:

If servers in default OU, for example, it works per normal.

image

Otherwise, if in non-default OU, for example below, need to pre-stage the virtual computer objects (VCOs) and grant full right of Cluster Computer Object (CNO) on VCOs.

image

Posted by Luo Donghua at 11:42 PM 20 comments
Labels: ,

Fix SPN registration issue for Windows return code: 0x21c7, state: 15.

 

Symptoms:

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/VMMSQL01.dbaglobe.com:1433 ] for the SQL Server service. Windows return code: 0x21c7, state: 15.

 

Table: UPN and SPN uniqueness error codes

image

image

The attribute value provided is not unique in the forest or partition. Attribute: servicePrincipalName Value=MSSQLSvc/VMMSQL01.dbaglobe.com
CN=VMMSQL01,OU=SQLServers,DC=dbaglobe,DC=com
Value=MSSQLSvc/VMMSQL01.dbaglobe.com
CN=VMMSQL01,OU=SQLServers,DC=dbaglobe,DC=com Winerror: 8647
See http://go.microsoft.com/fwlink/?LinkID=279782 for more details on this policy.

 

How to Troubleshoot:

C:\Users\Administrator>dsquery * -filter servicePrincipalName=*
"CN=VMMAD01,OU=Domain Controllers,DC=dbaglobe,DC=com"
"CN=VMMSQL01,CN=Computers,DC=dbaglobe,DC=com"
"CN=VMMSQL02,CN=Computers,DC=dbaglobe,DC=com"
"CN=krbtgt,CN=Users,DC=dbaglobe,DC=com"
"CN=sqlsvcs,CN=Users,DC=dbaglobe,DC=com"

C:\Users\Administrator>setspn -l VMMSQL01
Registered ServicePrincipalNames for CN=VMMSQL01,CN=Computers,DC=dbaglobe,DC=com:
        MSSQLSvc/VMMSQL01.dbaglobe.com:1433
        MSSQLSvc/VMMSQL01.dbaglobe.com
        WSMAN/VMMSQL01
        WSMAN/VMMSQL01.dbaglobe.com
        RestrictedKrbHost/VMMSQL01
        HOST/VMMSQL01
        RestrictedKrbHost/VMMSQL01.dbaglobe.com
        HOST/VMMSQL01.dbaglobe.com


C:\Users\Administrator>setspn -D MSSQLSvc/VMMSQL01.dbaglobe.com:1433 VMMSQL01
Unregistering ServicePrincipalNames for CN=VMMSQL01,CN=Computers,DC=dbaglobe,DC=com
        MSSQLSvc/VMMSQL01.dbaglobe.com:1433
Updated object

C:\Users\Administrator>setspn -D MSSQLSvc/VMMSQL01.dbaglobe.com VMMSQL01
Unregistering ServicePrincipalNames for CN=VMMSQL01,CN=Computers,DC=dbaglobe,DC=com
        MSSQLSvc/VMMSQL01.dbaglobe.com
Updated object

C:\Users\Administrator>setspn -l VMMSQL01
Registered ServicePrincipalNames for CN=VMMSQL01,CN=Computers,DC=dbaglobe,DC=com:
        WSMAN/VMMSQL01
        WSMAN/VMMSQL01.dbaglobe.com
        RestrictedKrbHost/VMMSQL01
        HOST/VMMSQL01
        RestrictedKrbHost/VMMSQL01.dbaglobe.com
        HOST/VMMSQL01.dbaglobe.com

image

Reference:

https://technet.microsoft.com/en-us/library/dn535779.aspx

http://blogs.technet.com/b/mdegre/archive/2009/11/20/the-sql-network-interface-library-was-unable-to-register-spn.aspx

https://social.technet.microsoft.com/Forums/systemcenter/en-US/aa0a8ad4-3598-4c01-ab7d-9c49c19071d4/2012r2-dc-ad-lds-service-principal-names-duplicates

Posted by Luo Donghua at 9:42 PM 0 comments
Labels: ,

Fix Service Principal Name (SPN) for SQL Server in Windows 2012 AD Environment

 When the Database Engine service starts, it attempts to register the Service Principal Name (SPN). If the account starting SQL Server doesn’t have permission to register a SPN in Active Directory Domain Services, this call will fail and a warning message will be logged in the Application event log as well as the SQL Server error log. To register the SPN, the Database Engine must be running under a built-in account, such as Local System (not recommended), or NETWORK SERVICE, or an account that has permission to register an SPN, such as a domain administrator account. When SQL Server is running on the Windows 7 or Windows Server 2008 R2 operating system, you can run SQL Server using a virtual account or a managed service account (MSA). Both virtual accounts and MSA’s can register an SPN. If SQL Server is not running under one of these accounts, the SPN is not registered at startup and the domain administrator must register the SPN manually.

Symptoms:

SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/VMMSQL01.dbaglobe.com ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/VMMSQL01.dbaglobe.com:1433 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

How to Fix:  (Example Service Account is DBAGLOBE\sqlsvcs)

image
image
image
image
image
If SELF is not listed, click Add, and then add SELF.
Click Edit
image
Ensure the following permissions under Permissions are selected:
  • Read servicePrincipalName
  • Write servicePrincipalName
image
Click Apply to apply the setting.
Modify the account to make sure the scope is “This object only”.
image
Check  the effective Access to ensure “Write servicePrincipalName” is granted.
image
image
Restart SQL Server Services
image

Grant the privilege via command line:

C:\Users\Administrator>dsacls CN=sqlsvcs2,CN=Users,DC=dbaglobe,DC=com /G SELF:RPWP;servicePrincipalName

Additional verification to make sure service account has “Validated write to service principal name” privilege

C:\Users\Administrator>hostname
VMMAD01
C:\Users\Administrator>dsacls CN=sqlsvcs,CN=Users,DC=dbaglobe,DC=com
Owner: DBAGLOBE\Domain Admins
Group: DBAGLOBE\Domain Admins
Access list:
Allow DBAGLOBE\Domain Admins          FULL CONTROL
Allow BUILTIN\Account Operators       FULL CONTROL
Allow NT AUTHORITY\Authenticated Usersq
                                      SPECIAL ACCESS
                                      READ PERMISSONS
Allow NT AUTHORITY\SELF               SPECIAL ACCESS
                                      READ PERMISSONS
                                      LIST CONTENTS
                                      READ PROPERTY
Allow NT AUTHORITY\SYSTEM             FULL CONTROL
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS   <Inherited from parent>
                                      READ PERMISSONS
                                      LIST CONTENTS
                                      READ PROPERTY
                                      LIST OBJECT
Allow DBAGLOBE\Enterprise Admins      FULL CONTROL   <Inherited from parent>
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS   <Inherited from parent>
                                      LIST CONTENTS
Allow BUILTIN\Administrators          SPECIAL ACCESS   <Inherited from parent>
                                      DELETE
                                      READ PERMISSONS
                                      WRITE PERMISSIONS
                                      CHANGE OWNERSHIP
                                      CREATE CHILD
                                      LIST CONTENTS
                                      WRITE SELF
                                      WRITE PROPERTY
                                      READ PROPERTY
                                      LIST OBJECT
                                      CONTROL ACCESS
Allow DBAGLOBE\RAS and IAS Servers    SPECIAL ACCESS for Account Restrictions
                                      READ PROPERTY
Allow DBAGLOBE\RAS and IAS Servers    SPECIAL ACCESS for Logon Information
                                      READ PROPERTY
Allow DBAGLOBE\RAS and IAS Servers    SPECIAL ACCESS for Group Membership
                                      READ PROPERTY
Allow DBAGLOBE\RAS and IAS Servers    SPECIAL ACCESS for Remote Access Information
                                      READ PROPERTY
Allow DBAGLOBE\Cert Publishers        SPECIAL ACCESS for userCertificate
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow BUILTIN\Windows Authorization Access Group
                                      SPECIAL ACCESS for tokenGroupsGlobalAndUniversal
                                      READ PROPERTY
Allow BUILTIN\Terminal Server License Servers
                                      SPECIAL ACCESS for terminalServer
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow BUILTIN\Terminal Server License Servers
                                      SPECIAL ACCESS for Terminal Server License Server
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow NT AUTHORITY\Authenticated Users
                                      SPECIAL ACCESS for General Information
                                      READ PROPERTY
Allow NT AUTHORITY\Authenticated Users
                                      SPECIAL ACCESS for Public Information
                                      READ PROPERTY
Allow NT AUTHORITY\Authenticated Users
                                      SPECIAL ACCESS for Personal Information
                                      READ PROPERTY
Allow NT AUTHORITY\Authenticated Users
                                      SPECIAL ACCESS for Web Information
                                      READ PROPERTY
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for Personal Information
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for Phone and Mail Options
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for Web Information
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Account Restrictions   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Logon Information   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Group Membership   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for General Information   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Remote Access Information   <Inherited from parent>
                                      READ PROPERTY
Allow NT AUTHORITY\ENTERPRISE DOMAIN CONTROLLERS
                                      SPECIAL ACCESS for tokenGroups   <Inherited from parent>
                                      READ PROPERTY
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for msDS-AllowedToActOnBehalfOfOtherIdentity   <Inherited from parent>
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for Private Information   <Inherited from parent>
                                      WRITE PROPERTY
                                      READ PROPERTY
                                      CONTROL ACCESS
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for Validated write to service principal name
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow Everyone                        Change Password
Allow NT AUTHORITY\SELF               Change Password
Allow NT AUTHORITY\SELF               Send As
Allow NT AUTHORITY\SELF               Receive As
Permissions inherited to subobjects are:
Inherited to all subobjects
Allow DBAGLOBE\Enterprise Admins      FULL CONTROL   <Inherited from parent>
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS   <Inherited from parent>
                                      LIST CONTENTS
Allow BUILTIN\Administrators          SPECIAL ACCESS   <Inherited from parent>
                                      DELETE
                                      READ PERMISSONS
                                      WRITE PERMISSIONS
                                      CHANGE OWNERSHIP
                                      CREATE CHILD
                                      LIST CONTENTS
                                      WRITE SELF
                                      WRITE PROPERTY
                                      READ PROPERTY
                                      LIST OBJECT
                                      CONTROL ACCESS
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for msDS-AllowedToActOnBehalfOfOtherIdentity   <Inherited from parent>
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for Private Information   <Inherited from parent>
                                      WRITE PROPERTY
                                      READ PROPERTY
                                      CONTROL ACCESS
Inherited to computer
Allow NT AUTHORITY\ENTERPRISE DOMAIN CONTROLLERS
                                      SPECIAL ACCESS for tokenGroups   <Inherited from parent>
                                      READ PROPERTY
Inherited to group
Allow NT AUTHORITY\ENTERPRISE DOMAIN CONTROLLERS
                                      SPECIAL ACCESS for tokenGroups   <Inherited from parent>
                                      READ PROPERTY
Inherited to computer
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for msTPM-TpmInformationForComputer   <Inherited from parent>
                                      WRITE PROPERTY
Inherited to group
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS   <Inherited from parent>
                                      READ PERMISSONS
                                      LIST CONTENTS
                                      READ PROPERTY
                                      LIST OBJECT
Inherited to inetOrgPerson
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS   <Inherited from parent>
                                      READ PERMISSONS
                                      LIST CONTENTS
                                      READ PROPERTY
                                      LIST OBJECT
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for General Information   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Group Membership   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Logon Information   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Account Restrictions   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Remote Access Information   <Inherited from parent>
                                      READ PROPERTY
The command completed successfully

References: http://mssqlwiki.com/tag/the-sql-server-network-interface-library-could-not-register-the-service-principal-name-spn/
Posted by Luo Donghua at 12:39 AM 3 comments
Labels: ,
Subscribe to: Posts (Atom)