SQL Server 設定 2008 Windows 服務帳戶

SQL Server 2008 線上叢書 (2008 年 5 月)
設定 Windows 服務帳戶

http://technet.microsoft.com/zh-tw/library/ms143504.aspx

How To Backup And Restore Windows Server 2003 IAS Service Configuration to a new Server

How To Backup And Restore Windows Server 2003 IAS Service Configuration to a new Server

1.Old Server
C:\netsh aaaa show config > C:\ISA_Backup.txt

2.New Server
C:\netsh exec ISA_Backup.txt

檢查 Windows Server 2003 是否已啟動

檢查 Windows Server 2003 是否已啟動
1.開始->執行
2.輸入oobe/msoobe /a
3.按 [確定]

如何在快速啟動列上重新建立顯示桌面圖示

如何在快速啟動列上重新建立顯示桌面圖示

1.啟動notepad記事本,輸入以下文字內容:
[Shell]
Command=2
IconFile=explorer.exe,3
[Taskbar]
Command=ToggleDesktop
2.[檔案]->[另存新檔]->儲存在桌面上為 [Show Desktop.scf]
3.將[Show Desktop.scf]拖曳到快速啟動列

How to compact VHD file 如何縮小VHD檔(Hyper-V and Virtual Server 2005)

How to compact VHD file 如何縮小VHD檔

[Hyper-V]
1.Virtual Machine開機，登入Windows系統(Guest OS)，執行磁碟重組
2.下載Sysinternals Suite
    http://technet.microsoft.com/en-us/sysinternals/bb842062
   或只下載SDelete
   http://technet.microsoft.com/en-us/sysinternals/bb897443
3.在Windows系統(Guest OS)執行sdelete c:/ -z
4.Virtual Machine關機
5.Hyper-V Manager使用[編輯虛擬硬碟精靈]壓縮VHD檔
  http://technet.microsoft.com/zh-tw/library/cc755149(WS.10).aspx


[Virtual Server]
1.Virtual Server Administration Website
mount C:\Program Files\Microsoft Virtual Server\Virtual Machine Additions\Precompactor.iso

2.run Precompact.exe in Guest OS

3.shutdown Guest OS

4.Virtual Server Administration Website
(1)Under Virtual Disks, click Inspect(檢查)(2)In the Known virtual hard disks list, click the virtual hard disk that you want to compact
Note You can also type the path of the virtual hard disk in the Fully qualified path to file box.
(3)Click Inspect
(4)Under VirtualHardDiskName.vhd Actions, click Compact virtual hard disk, and then click Compact(壓縮).


[Virtual Server 2005 R2 Release Notes]
you can find Virtual Disk Precompactor.iso in the folder: %systemdrive%\Program Files\Microsoft Virtual Server\Virtual Machine Additions\.

To use Virtual Disk Precompactor, you must first capture Virtual Disk Precompactor.iso using the standard procedure for capturing .iso files as described in "To add or remove a CD or DVD drive" in the Virtual Server 2005 Administrator's Guide.

If your virtual machine is running when you capture Virtual Disk Precompactor.iso, Virtual Disk Precompactor starts automatically. If you click Yes when prompted, Virtual Disk Precompactor precompacts all virtual hard disks for this virtual machine. If Virtual Disk Precompactor does not start automatically, you can start it by navigating to the CD drive of the guest operating system and double-clicking Precompact.exe.

Alternatively, you can also run Virtual Disk Precompactor from the command line by navigating to the CD drive of the guest operating system and typing Precompact. The available parameters are:

-Help - Displays help for the precompactor.

-Silent - Runs the precompactor in unattended mode.

-SetDisks - Specifies the disks to be precompacted. If this parameter isn't specified, then all virtual hard disks for this virtual machine are compacted. For example, the following command will precompact your C, D, and E drives in unattended mode:

Precompact -Silent -SetDisks:CDE
Note
You can only compact dynamically expanding VHDs. To compact a fixed-size VHD, you can convert it to a dynamically expanding VHD, compact it, and then convert it back to a fixed size VHD.

[Reference]
http://support.microsoft.com/default.aspx?scid=kb;en-us;888760

How to use the Sysprep tool to automate successful deployment of Windows XP

How to use the Sysprep tool to automate successful deployment of Windows XP
http://support.microsoft.com/kb/302577/en-us

[以下節錄from上面的微軟KB]
Prepare a reference computer for Sysprep deployment

To prepare a reference computer for Sysprep deployment in Windows XP, follow these steps:
1.On a reference computer, install the operating system and any programs that you want installed on your destination computers.

2.Click Start, click Run, type cmd, and then click OK.

3.At the command prompt, change to the root folder of drive C, and then type md Sysprep.

4.Insert the Windows XP operating system CD into the appropriate CD-ROM or DVD-ROM drive, and then open the Deploy.cab file in the \Support\Tools folder.

Note To make sure that you are using the correct version of the Deploy.cab file for your service pack, use the Deploy.cab file that is distributed with that service pack. Visit the following Microsoft Web site to download the correct version for your service pack:
http://www.microsoft.com/windowsxp/downloads/default.mspx (http://www.microsoft.com/windowsxp/downloads/default.mspx)

5.Copy the Sysprep.exe file and the Setupcl.exe file to the Sysprep folder.

Note If you use the Sysprep.inf file, copy this file to the Sysprep folder also. For the Sysprep tool to function correctly, the Sysprep.exe file, the Setupcl.exe file, and the Sysprep.inf file must all be in the same folder.

6.Remove the Windows XP operating system CD.

7.At the command prompt, type cd Sysprep to change to the Sysprep folder.

8.To run the Sysprep tool, type the following command at the command prompt:
Sysprep /optional parameter
Note For a list of parameters, see the "Sysprep parameters" section.

If you run the Sysprep.exe file from the %systemdrive%\Sysprep folder, the Sysprep.exe file removes the whole folder and the contents of the folder after it finishes.

9.Microsoft recommends that after you run the Sysprep.exe file on a reference computer, isolate the reference computer from the local network where the image will be subsequently deployed to avoid any NetBIOS conflicts. This permits the Sysprep tool to complete the Setup program, join the domain that is specified, and use the new computer name that is specified in the Sysprep.inf answer file.

[Other Reference]
http://technet.microsoft.com/en-us/library/bb457073.aspx

如何在遺失了HP DL360G伺服器驅動程式光碟,只下載一個工具即可安裝所有驅動程式

如何在遺失了HP DL360G伺服器驅動程式光碟,只下載一個工具即可安裝所有驅動程式

1.從HP網站下載工具程式
ProLiant 支援套件適用於 Microsoft Windows Server 2003 v8.25









2.執行下載的psp-8.25.w2k3.i386.exe檔,會要求指定一個解壓縮的位置,然在再執行裡面的setup.exe

3.隨著GUI畫面的步驟就會開始偵測需要安裝會升級的Driver,按Install就會開始安裝了,非常方便

Scripting Tool and Document Referencce List

[Help Document and Reference]
Windows Script 5.5-VBS Document
Windows Script 5.6 Documentation 2001
Windows Script 5.6 Documentation 2006-04-27
ADO Help
ADSI Help and Document
Script Center All-in-One
TechNet Script Center
scriptcenter_2.0.chm
scriptcenter_3.0.chm
Scripting Guy
Sesame Script
WMI Help
Learn WMI.chm
Group Policy Reference

[Tools and Script Engine]
Script Encoder
Script Editor
Script Engine v5.6 , v5.7
Scriptomatic (Code Generator)
ADSI Scriptomatic
Scriptomatic Tool
Scriptomatic_V2
Tweakomatic

[Debug Tools]
Script Debugger for Windows NT 4.0 and Later 2005-07-14

[Other Utilities]
Event Query
Get OS Version
Log Parser 2.2
Log Windows Event
Subincal Utility
WMI Collection
The WMI Diagnosis Utility
WMI Administrative Tools
WMI Code Creator v1.0
WMI Explorer 1.00
Workstation Name Changer
Xcacls Utility

Microsoft Exchange Server 2007 Management Tools (32-Bit)

Microsoft Exchange Server 2007 Management Tools (32-Bit)

http://www.microsoft.com/downloads/details.aspx?familyid=6BE38633-7248-4532-929B-76E9C677E802&displaylang=en

Brief Description
Exchange Server 2007 is a native 64-bit application that includes 64-bit management tools. You can use the management tools to administer your Exchange Server environment remotely. If the remote computer is running a 32-bit operating system, you will need to download the 32-bit management tools.

Quick Details
Version: 685.25
Date Published: 5/7/2008
Language: English
Download Size: 686.7 MB - 1527.3 MB*
*Download size depends on selected download components

System Requirements
Supported Operating Systems: Windows Server 2003 R2 (32-Bit x86); Windows Server 2003 Service Pack 1; Windows XP

Exchange System Manager for Windows Vista

Exchange System Manager for Windows Vista
http://www.microsoft.com/downloads/details.aspx?FamilyID=3403d74e-8942-421b-8738-b3664559e46f&DisplayLang=en#filelist

Brief Description
Exchange System Manager for Exchange Server 2003 running on the Windows Vista operating system

Quick Details
Version: 1.0
Date Published: 8/6/2008
Language: English
Download Size: 46 KB - 24.5 MB*
*Download size depends on selected download components.

Overview
This download will allow installing and operating the Exchange System Manager for Microsoft Exchange Server 2003 on Windows Vista OS

System Requirements
Supported Operating Systems: Windows Vista

SQL Server 2008 Books Online 線上叢書 May 2009

SQL Server 2008 Books Online 線上叢書 May 2009
[英文]
Microsoft SQL Server 2008 Books Online (May 2009)
Quick Details
File Name: SQLServer2008_BOL_May2009.msi
Version: 10.00.1601.22
Date Published: 5/21/2009
Language: English
Download Size: 144.5 MB

[繁體中文]
Microsoft SQL Server 2008 線上叢書 (2009 年 5 月)
快速明細
檔案名稱： SQLServer2008_BOL_May2009_CHT.msi
版本： 10.00.1601.22
發佈日期： 2009/6/15
語言： 繁體中文
下載項目大小： 166.0 MB

如何更改Windows XP, Vista產品金鑰(序號)

1.官方方法
1.1.進入下面網頁[ 更新產品金鑰 ] 選擇Windows XP或Vista, 或直接點選下面連結
Windows Product Key Update Tool 操作指示
或用
1.2手動更新法(only for Windows XP)
(1)[開始]→[執行]→[RegEdit]，按下確定
(2)打開HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\WPAEvents，
將[OOBETimer]的最後兩個數值 24 97 刪除，按下[確定]，關閉regedit程式
(3)在[開始]→[執行]，輸入[oobe/msoobe /a]，按下[確定]，
進入[啟動Windows] → 選擇第2項[是，我想打電話給客戶服務代表來啟用Windows]，
按下[確定]，進入下一個視窗
選擇[變更產品金鑰]
修改產品金鑰，完成後選擇[更新] (改成您的正式版序號)
選擇[稍後再提醒我]
(4)重新啟動電腦後在[開始]→[執行]，輸入[oobe/msoobe /a]，
按下[確定] 進入[啟動Windows]時應該顯示已經啟動


2.Third party工具
Keyfinder
http://www.magicaljellybean.com/keyfinder/

整合資料加密到資料庫安全設計Data Encryption

[實作快速摘要]
Step1.確認與建立資料庫主要金鑰(Database Master Key)
Step2.建立Certificate準備來加密Symmetric Key
Step3.建立Symmetric Key時使用Certificate來加密
Step4.資料表加入一個加密欄位,加密欄位型別Data Type建議設定為varbinary
Step5.資料寫入時,使用Symmetric Key來加密欄位
PS.詳細Code請看後面的[combine Certificate and Symmetric Key]

[SQL Server 2005的資料加密技術]
1.對稱式金鑰 Symmetric Key
只用一把Key,相對於Asymmetric Key與Certificate較有效率,
但Asymmetric Key與Certificate加密安全性較高
例如Service Master Key與Database Master Key皆為Symmetric Key,

SELECT * FROM master.sys.symmetric_keys
SELECT * FROM sys.symmetric_keys

[寫入]
OPEN SYMMETRIC KEY KeyName DECRYPTION BY PASSWORD = 'Password'
EncryptByKey(Key_GUID('symKey_Name'), 'Insert_Value')
CLOSE SYMMETRIC KEY KeyName

[讀取]
OPEN SYMMETRIC KEY KeyName DECRYPTION BY PASSWORD = 'Password'
DecryptByKey([ColumnName])
CLOSE SYMMETRIC KEY KeyName

2.非對稱式金鑰 Asymmetric Key

SELECT * FROM sys.ssymmetric_keys

[寫入]
EncryptByAsymKey( AsymKey_ID('asymKey_Name'),'Insert_Value')

[讀取]
DecryptByAsymKey( AsymKey_ID('asymKey_Name'), [ColumnName], N'asymKey_Password')

3.憑證 Certificate

[寫入]
EncryptByCert( Cert_ID('Cert_Name'), 'Insert_Value' )
[讀取]
DecryptByCert( Cert_ID('Cert_Name'),[ColumnName], N'Cert_Password')


-- ====== [Service Master Key 與 Database Master Key] ==============
SELECT * FROM master.sys.symmetric_keys
GO

-- Backup Service Master Key
USE Master
GO
BACKUP SERVICE MASTER KEY
TO FILE='C:\Backup\SQL2K5.smk'
ENCRYPTION BY PASSWORD='3dH85Hhk003GHk2597gheij4';
GO

-- Restore Service Master Key
RESTORE SERVICE MASTER KEY
FROM FILE='C:\Backup\SQL2K5.smk'
DECRYPTION BY PASSWORD='3dH85Hhk003GHk2597gheij4';
GO

-- Regenerate Service Master Key
--ALTER SERVICE MASTER KEY REGENERATE
--GO

-- Create Demo Database
CREATE DATABASE [EncryptionDB]

-- Database Master Key
USE [EncryptionDB]
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD='23987hxJ#KL95234nl0zBe';
GO

SELECT [name] N'資料庫'
, [is_master_key_encrypted_by_server] N'已使用主要金鑰加密的資料庫'
FROM master.sys.databases;
GO

SELECT * FROM sys.symmetric_keys;
GO

-- ALTER MASTER KEY
--ALTER MASTER KEY
-- REGENERATE WITH ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';
--GO

-- Backup Database MASTER KEY
BACKUP MASTER KEY
TO FILE = 'C:\Backup\EncryptionDB_MasterKey.dmk'
ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';
GO

-- Restore Database Master Key
RESTORE MASTER KEY
FROM FILE = 'C:\Backup\EncryptionDB_MasterKey.dmk'
DECRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';
GO


-- ====== [Symmetric Key] ==============
USE [EncryptionDB]
GO
-- Create test table
CREATE TABLE dbo.tSYMMETRIC
(CustomerID int NOT NULL PRIMARY KEY,
PasswordHintQuestion nvarchar(300) NOT NULL,
PasswordHintAnswer varbinary(8000) NOT NULL)
GO

-- CREATE SYMMETRIC KEY
CREATE SYMMETRIC KEY sym01
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = '25ho878!45fR6HG%B3f';

--
SELECT * FROM sys.symmetric_keys

-- Encryption Data With EncryptByKey()
OPEN SYMMETRIC KEY sym01
DECRYPTION BY PASSWORD = '25ho878!45fR6HG%B3f';

INSERT dbo.tSYMMETRIC (CustomerID
,PasswordHintQuestion
,PasswordHintAnswer)
VALUES (1
, N'大人小朋友的可玩的'
, EncryptByKey(Key_GUID('sym01 '), 'Wii')
)

CLOSE SYMMETRIC KEY sym01;

-- Query
SELECT CustomerID
,PasswordHintQuestion
,CAST(DecryptByKey(PasswordHintAnswer) as varchar(4000)) N'密碼提示的答案'
FROM dbo.[tSYMMETRIC]

-- Query With DecryptByKey()
OPEN SYMMETRIC KEY sym01
DECRYPTION BY PASSWORD = '25ho878!45fR6HG%B3f';

SELECT CustomerID
,PasswordHintQuestion
,CAST(DecryptByKey(PasswordHintAnswer) as varchar(4000)) N'密碼提示的答案'
FROM dbo.[tSYMMETRIC]

CLOSE SYMMETRIC KEY sym01

-- DELETE SYMMETRIC KEY
DROP SYMMETRIC KEY sym01;


-- ====== [Asymmetric Key] ==============

-- Create TEST Table dbo.tASY
CREATE TABLE dbo.[tASY]
(
[EmployeeID] int NOT NULL PRIMARY KEY,
[mSalary] varbinary(8000) NOT NULL
)
GO

-- CREATE ASYMMETRIC KEY
CREATE ASYMMETRIC KEY asym01
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'bmsA$dk7i82bv55foajsd9764';
GO

--
SELECT [name] N'金鑰的名稱'
,[pvt_key_encryption_type_desc] N'私密金鑰加密方式'
,[algorithm_desc] N'金鑰使用的演算法'
FROM sys.[asymmetric_keys]

--EX1. EncryptByAsmKey()
INSERT dbo.[tASY]
VALUES (1
,EncryptByAsymKey(AsymKey_ID('asym01'),'999999')
)

--
SELECT * FROM dbo.[tASY]

--
SELECT [EmployeeID]
,CAST([mSalary] AS varchar(2000)) N'薪資'
FROM dbo.[tASY]

--EX2. DecryptByAsymKey()
SELECT [EmployeeID]
,CAST(
DecryptByAsymKey(
AsymKey_ID('asym01'), mSalary, N'bmsA$dk7i82bv55foajsd9764'
) as varchar(100)
) N'薪資'
FROM dbo.[tASY]

-- ====== [Certificate] ==============

-- 建立資料表 dbo.tCert
CREATE TABLE dbo.[tCert]
(
[uids] int NOT NULL PRIMARY KEY,
[cardid] varbinary(8000) NOT NULL
)
GO

-- CREATE CERTIFICATE
CREATE CERTIFICATE cs01
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Wii issue',
START_DATE = '2009/08/08',
EXPIRY_DATE= '2009/12/31'
GO

--
SELECT [name] N'憑證名稱'
,[pvt_key_encryption_type_desc] N'私密金鑰加密方式'
,issuer_name N'憑證發行者'
,[start_date] N'憑證生效時間'
,[expiry_date] N'憑證逾期時間'
FROM sys.[certificates]

--EX1.
-- EncryptByCert()
INSERT dbo.tCert
VALUES (1
,EncryptByCert(Cert_ID('cs01'), '55553635401028')
)

--
SELECT * FROM dbo.tCert

--
SELECT [uids]
,CAST([cardid] AS varchar(2000)) N'卡號'
FROM dbo.tCert

-- DecryptByCert()
SELECT [uids]
,CAST(
DecryptByCert(
Cert_ID('cs01'),[cardid], N'pGFD4bb925DGvbd2439587y'
) as varchar(1000)
) N'卡號'
FROM dbo.[tCert]


-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
--EX2. Backup Certificate
BACKUP CERTIFICATE cs01
TO FILE = 'C:\Backup\SQLServer\cs01.cer'
WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y' ,
FILE = 'C:\Backup\SQLServer\cs01PK.pvk' ,
ENCRYPTION BY PASSWORD = 'zxxn34khUbhk$w4ecJH5gh' );
GO

-- 刪除 CERTIFICATE
DROP CERTIFICATE cs01

--
SELECT [uids]
,CAST(
DecryptByCert(
Cert_ID('cs01'),[cardid], N'pGFD4bb925DGvbd2439587y'
) as varchar(1000)
) N'卡號'
FROM dbo.[tCert]


--
SELECT [name] N'憑證名稱'
,[pvt_key_encryption_type_desc] N'私密金鑰加密方式'
,[issuer_name] N'憑證發行者'
,[start_date] N'憑證生效時間'
,[expiry_date] N'憑證逾期時間'
FROM sys.[certificates];

-- Restore Certificate
CREATE CERTIFICATE cs01
FROM FILE = 'C:\Backup\SQLServer\cs01.cer'
WITH PRIVATE KEY (FILE = 'C:\Backup\SQLServer\cs01PK.pvk',
DECRYPTION BY PASSWORD = 'zxxn34khUbhk$w4ecJH5gh',
ENCRYPTION BY PASSWORD ='pGFD4bb925DGvbd2439587y');
GO

--
SELECT [uids]
,CAST(
DecryptByCert(
Cert_ID('cs01'),[cardid], N'pGFD4bb925DGvbd2439587y'
) as varchar(1000)
) N'卡號'
FROM dbo.[tCert];


-- ====== [combine Certificate and Symmetric Key] ==============

CREATE TABLE dbo.EmployeeReview
(EmployeeID int NOT NULL,
ReviewDate datetime DEFAULT GETDATE() NOT NULL,
Comments varbinary(8000) NOT NULL)
GO

--02 建立 CERTIFICATE
CREATE CERTIFICATE cs02
WITH SUBJECT = 'PS3 issue',
START_DATE = '2009/08/08',
EXPIRY_DATE= '2009/12/31'
GO

--03 建立 SYMMETRIC KEY
CREATE SYMMETRIC KEY sym03
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE cs02 -- 利用 cs02 憑證來保護 sym03 對稱金鑰
GO

--
SELECT * FROM sys.symmetric_keys
WHERE [NAME] <> '##MS_DatabaseMasterKey##'
--
SELECT [name] N'憑證名稱'
,[pvt_key_encryption_type_desc] N'私密金鑰加密方式'
,[issuer_name] N'憑證發行者'
,[start_date] N'憑證生效時間'
,[expiry_date] N'憑證逾期時間'
FROM sys.certificates

--04 EncryptByKey()
OPEN SYMMETRIC KEY sym03
DECRYPTION BY CERTIFICATE cs02

INSERT INTO dbo.EmployeeReview
VALUES (1
,DEFAULT
,EncryptByKey(Key_GUID('sym03'),N'加薪到 $99,000')
);

CLOSE ALL SYMMETRIC KEYS

--
SELECT * FROM dbo.EmployeeReview

--
SELECT
EmployeeID
,ReviewDate
,CONVERT(varchar,Comments) AS Comments
FROM dbo.EmployeeReview;

--05 DecryptByKey()
OPEN SYMMETRIC KEY sym03
DECRYPTION BY CERTIFICATE cs02

SELECT
EmployeeID
,ReviewDate
,CONVERT(nvarchar,DecryptByKey(Comments)) AS Comments
FROM dbo.EmployeeReview

CLOSE ALL SYMMETRIC KEYS

--
USE master
GO
DROP DATABASE EncryptionDB
GO