SQL-DMO 程式化管理 SQL Server 驗證模式：VBA 完整程式碼範例

前言

SQL-DMO (SQL Distributed Management Objects) 允許以程式碼控制 SQL Server 設定。本文提供 VBA 完整範例：SQL Server 驗證登入、Windows 整合驗證、切換驗證模式、開啟 ADP 專案。

核心物件模型

• SQLDMO.SQLServer：伺服器連線與管理主物件
• .Connect(server, uid, pwd) / .LoginSecure = True (Windows 驗證)
• .IntegratedSecurity.SecurityMode：SQLDMOSecurity_Integrated (僅 Windows) / SQLDMOSecurity_Mixed (混合)
• .Stop / .Start：變更驗證模式後需重啟服務生效

範例 1：SQL Server 驗證登入

Sub SQLDMOSQLServerLogin(srvname As String, suid As String, pwd As String)
    Dim srv As SQLDMO.SQLServer
    Set srv = New SQLDMO.SQLServer
    srv.Connect srvname, suid, pwd
    ' 執行管理工作...
    srv.Disconnect
    Set srv = Nothing
End Sub

範例 2：Windows 整合驗證登入

Sub SQLDMOWindowsLogin(srvname As String)
    Dim srv As SQLDMO.SQLServer
    Set srv = New SQLDMO.SQLServer
    srv.LoginSecure = True  ' 關鍵：啟用 Windows 整合驗證
    srv.Connect srvname
    srv.Disconnect
    Set srv = Nothing
End Sub

範例 3：切換驗證模式 (需重啟服務)

Sub ChangeServerAuthenticationMode(authMode As Long)
    ' authMode: SQLDMOSecurity_Integrated (3) 或 SQLDMOSecurity_Mixed (4)
    Dim srv As SQLDMO.SQLServer
    Set srv = New SQLDMO.SQLServer
    srv.LoginSecure = True  ' 必須用 Windows 驗證連線才能修改此設定
    srv.Connect "(local)"
    
    srv.IntegratedSecurity.SecurityMode = authMode
    srv.Disconnect
    
    ' 必須完全停止再啟動服務生效
    srv.Stop
    Do Until srv.Status = SQLDMOSvc_Stopped
        DoEvents
    Loop
    srv.Start True, "(local)"
    
    srv.Disconnect
    Set srv = Nothing
End Sub

範例 4：開啟 Access ADP 專案 (支援兩種驗證)

Sub OpenADPWindowsOrSQLServer(srvname, dbname, prpath, prname, _
    suid, pwd, useWindowsLogin As Boolean)
    
    Dim appAccess As Access.Application
    Set appAccess = CreateObject("Access.Application.9") ' 2000=.9, 2002=.10
    
    appAccess.OpenAccessProject prpath & "\" & prname
    appAccess.Visible = True
    
    If useWindowsLogin Then
        appAccess.CurrentProject.OpenConnection _
            "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;" & _
            "PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=" & dbname & _
            ";DATA SOURCE=" & srvname
    Else
        Dim connStr As String
        connStr = "PROVIDER=SQLOLEDB.1;INITIAL CATALOG=" & dbname & _
                  ";DATA SOURCE=" & srvname
        appAccess.CurrentProject.OpenConnection connStr, suid, pwd
    End If
    
    ' 視需求決定是否關閉 Access 實例
End Sub

關鍵注意事項

• 修改 SecurityMode **必須**以 Windows 整合驗證登入 (LoginSecure = True)
• 變更後 **必須完整停止並重啟服務** (Stop 等待 SQLDMOSvc_Stopped → Start)
• SQL-DMO 需安裝 SQL Server 用戶端工具 (含 sqldmo.dll)
• VBA 專案需加入參考：Microsoft SQLDMO Object Library
• Access 版本對應：2000 = Access.Application.9，2002 = Access.Application.10

適用場景

• 自動化部署腳本設定驗證模式
• 管理工具整合 SQL Server 設定
• ADP 專案動態切換連線驗證方式