title: Excel to SQL Security
body: <p>I use the VBA script below in an Excel module to allow a connection to a SQL server with a preprogramed username and password. The connection runs a basic select query however I need to hide or encrypt the password. The only methods I can think of are hiding the password in another sheet, locking the VBA or to call a text file from a shared drive that contains the password. However these methods still seem unsecure. Does anyone know of a better security method that can be implemented within the excel VBA?</p>
  
  <p>Sub ConnectSqlServer()</p>
  
  <pre><code>Dim txt As String
  txt = Sheets("Sheet1").Range("D1").Value
  If txt Like "*[A-Z]*" Or txt Like "*[;]*" Or txt Like "*[""]*" Then
      a = MsgBox("Invalid" &amp; vbNewLine &amp; "Try again", okonly + vbCritical, "SECURITY WARNING")
  Exit Sub
  
  Else
  
  End If
  
  Dim conn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim sConnString As String
  
  sConnString = "Provider=SQLOLEDB;Data Source=TESTSQLSERVER2012;" &amp; _
                "Initial Catalog=MASTER;" &amp; _
                "User ID=LOGINREADUSER;" &amp; _
                "Password=123456789;"
  
  Set conn = New ADODB.Connection
  Set rs = New ADODB.Recordset
  
  conn.Open sConnString
  conn.CommandTimeout = 0
  
  Set rs = conn.Execute("SELECT [ACCOUNT],[NAME].[DOB]" &amp; _
          "from [MASTER].[dbo].[CUSTOMER]" &amp; _
          ";")
  
  If Not rs.EOF Then
      Sheets("Sheet1").Range("A4").CopyFromRecordset rs
      rs.Close
  Else
      MsgBox "Error: No records returned.", vbCritical
  End If
  
  If CBool(conn.State And adStateOpen) Then conn.Close
  Set conn = Nothing
  Set rs = Nothing
  
  End Sub
  </code></pre>