[MSSQL] Create a request certification for MSSQL

產生需求憑證給MSSQL’s SSL：

;—————– request.inf —————–
[Version]
Signature="$Windows NT$"
[NewRequest]
Subject = "CN=FQDN" ; replace with the FQDN of the DC
KeySpec = 1
KeyLength = 2048
; Can be 1024, 2048, 4096, 8192, or 16384.
; Larger key sizes are more secure, but have
; a greater impact on performance.
Exportable = FALSE
MachineKeySet = TRUE
SMIME = False
PrivateKeyArchive = FALSE
UserProtected = FALSE
UseExistingKeySet = FALSE
ProviderName = "Microsoft RSA SChannel Cryptographic Provider"
ProviderType = 12
RequestType = PKCS10
KeyUsage = 0xa0
[EnhancedKeyUsageExtension]
OID=1.3.6.1.5.5.7.3.1 ; this is for Server Authentication
;———————————————–
[Extensions]
    2.5.29.17 = "{text}"
    _continue_ = "DNS=name1&"
    _continue_ = "DNS=name2"

;—————– END Of request.inf —————–

c:\>certreq –new request.inf output_file_for_CAtoSign.req

Then you can sign this .req, and import it to server that generates it. And you can find out this certificate for MSSQL SSL Encryption.

[MSSQL] How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console

Refer:

• How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console
  https://support.microsoft.com/en-us/kb/316898
• Certreq
  https://technet.microsoft.com/en-us/library/dn296456(v=ws.11).aspx
• How to enable LDAP over SSL with a third-party certification authority
  

 

• 問題1：無法載入使用者指定的憑證 [Cert Hash(sha1) "*****************"]。伺服器將不會接受連接。您應該確認已正確安裝憑證。請參閱線上叢書中的＜設定 SSL 使用的憑證＞。
  解答1：匯入憑證後，需再設定憑證權限，在憑證點選右鍵=>選「所有工作」=>選「管理私密金鑰」，設本機的 NT Service\MSSQLSERVER 權限可讀。

[MSSQL] ident 數值查詢

查詢目前識別值

select o.name, c.name , c.type , IDENT_CURRENT(o.name) 目前的識別值, IDENT_INCR(o.name) 遞增值
from syscolumns c inner join sysobjects o on c.id=o.id   
where c.status & 0x80 = 0x80 and o.type='U'

Refer: http://diary.tw/tim/search/identity

MSSQL查詢資料Row(s) to String

Words: row to one line, rows to string

已存在資料如下：
代碼表(code table):
code name
A apple
B banana
L lemon


資料列(data):
id name fruit_code quota rest
1 Alice A 50 12
2 Alice B 40 9
3 Bob L 30 22
4 Bob A 30 8

預期查詢結果如下輸出：

查詢結果(result)：

name Total Rest Detail
Alice 69 A-apple(38),B-banana(31)
Bob 30 L-lemon(8),A-apple(22)

SQL語法:

select 
  name
  , sum(quota)-sum(rest) 'Total Rest'
  ,STUFF(
     (select 
           ','
          +cast(fruit_code as varchar)
          +'-'
          +(select name from fruit_code t3 where t3.code=t1.fruit_code)
          +'('+cast(quota-rest as varchar)+')'
      from usage t1 
      where t1.name=t2.name 
      for xml path(''))
     ,1
     ,1
     ,''
  ) Detail
from usage t2
group by name