Remove UIDs from connection strings that use Trusted_Connection=Yes
#181
Comments
|
I agree. I have a function I use to switch from test to production that could probably be used. I won't be able to get to this until Monday right now unfortunately. |
|
Looked up regex call (this probably needs re-written, but it works, and there are other things that need rewritten and don't, soooo). Th rest of the routine to re-target the tables will need more redaction as it's pretty tied to my environment at the moment, but this was the hard part from what I remember developing this portion a while back. I also have a note in my code to look at StackOverflow: Regex VBA, in case that's helpful in the interim. Should be able to get the UID by changing You will also need to reference in Function FindODBC_Server(ByVal sourcestring As String) As String
Dim objRE As New RegExp
Dim Wynik As Variant
'wynik is used here instead of the function return string to allow shorter debugging (for now) But honestly
'you're probably NEEEEVer ever going to change this, so this note
'will sit in perpetuity.
'this has been written to grab a connection string, return the server ID/name, and return it
With objRE
.Global = True
.ignorecase = True
.pattern = "(.*)(SERVER=)([^; \r\n]*)(;?.*)"
Wynik = .replace(sourcestring, "$3") 'only 2nd part of the pattern will be returned
End With
FindODBC_Server = Wynik
' MsgBox (Wynik)
Debug.Print Wynik
End Function |
|
Try this out:
Should return the stripped out UID; testing to ensure |
|
Okay, I have an update that I have tested on both the testing database, and another database with linked SQL server tables. Looks like it is working great on both export and import. (Note that the UID values are automatically recreated on import, but that shouldn't be a problem since they are stripped out on export.) |
Removes UID and PWD values from connection strings where `Trusted_Connection=True`. These values are not needed in this context, and will be different when the database is built and exported by different users. Closes #181
The credentials should be preserved (exported) when not using a trusted connection with a linked table. See #181
|
RegEx is cool! I ended up taking a bit simpler approach that I hope is easier to read and maintain in the long run, especially since we have at least three different things we are modifying/adjusting in the connection string. Basically I just split the connection string by the semi-colon delimiters, and rebuilt the string piece by piece, making any necessary adjustments to the parts individually. '---------------------------------------------------------------------------------------
' Procedure : SanitizeConnectionString
' Author : Adam Waller
' Date : 2/26/2021
' Purpose : Sanitize the connection string by removing unneeded information and
' : converting database path to relative.
'---------------------------------------------------------------------------------------
'
Private Function SanitizeConnectionString(strConnection As String) As String
Dim lngPart As Long
Dim varParts As Variant
Dim strPart As String
If strConnection = vbNullString Then Exit Function
' Create array of connection string parts
varParts = Split(strConnection, ";")
' Loop through parts, building new connection string
With New clsConcat
.AppendOnAdd = ";"
For lngPart = 0 To UBound(varParts)
strPart = CStr(varParts(lngPart))
Select Case True
' Check for username/password
Case StartsWith(strPart, "UID=", vbTextCompare), _
StartsWith(strPart, "PWD=", vbTextCompare)
' These values are not needed when using a trusted connection.
If (InStr(1, strConnection, "Trusted_Connection=Yes", vbTextCompare) = 0) _
Or Not Options.AggressiveSanitize Then
' Retain the values if not using trusted connection, or if
' AggressiveSanitize option is set to false (Defaults to true).
.Add strPart
End If
' Check database path to convert to relative
Case StartsWith(strPart, "DATABASE=", vbTextCompare)
.Add GetRelativeConnect(strPart)
' Add all other sections
Case Else
.Add strPart
End Select
Next lngPart
' Remove trailing semicolon, and return string
.Remove 1
SanitizeConnectionString = .GetStr
End With
End Function |
|
Figured it out! Try this: Strip out UID if Trusted Connection is found
|
Uses `Yes` not `True`. Oops! See #181
|
Return |
|
Ok, last one: (Small fix, as my test didn't include both UID and PWD in one of the test cases).
Should strip out PWD and UID if it's trusted, and ignore it if not. ODBC;Description=MyProgram;DRIVER=SQL Server;SERVER=DbServer;UID=johndoe;PWD="WFEFE";Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=MySQLDatabase
ODBC;Description=MyProgram;DRIVER=SQL Server;SERVER=DbServer;UID=johndoe;PWD="WFEFE";APP=Microsoft Office 2010;DATABASE=MySQLDatabase
ODBC;Description=MyProgram;DRIVER=SQL Server;SERVER=DbServer;UID=johndoe;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=MySQLDatabase
ODBC;Description=MyProgram;DRIVER=SQL Server;SERVER=DbServer;PWD=johndoe;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=MySQLDatabaseReturns: (with $1 as the substitution rule) Updated Funcion: Function SanitizeConnection(ByVal sourcestring As String) As String
Dim objRE As New RegExp
Dim ReturnString As Variant
'this has been written to grab a connection string, return the server ID/name, and return it
With objRE
.Global = True
.ignorecase = True
.pattern = "(.*)(;UID=)[^ \r\n]*(?=;Trusted_Connection=Yes)|(;PWD=)[^ \r\n]*(?=;Trusted_Connection=Yes)"
ReturnString = .replace(sourcestring, "$1") 'Return the sanitized string.
End With
SanitizeConnection= ReturnString
End Function |
|
I'm sorry, I can't help myself; this one should cut down on memory use as it only requires a pointer and directly edits the string in place. Private Sub SanitizeConnection(ByRef SourceString As String)
Dim objRE As New RegExp
Dim ReturnString As Variant
'this has been written to grab a connection string, return the sanitized string.
With objRE
.Global = True
.ignorecase = True
.pattern = "(.*)(;UID=)[^ \r\n]*(?=;Trusted_Connection=Yes)|(;PWD=)[^ \r\n]*(?=;Trusted_Connection=Yes)"
sourcestring = .replace(sourcestring, "$1") 'Return the sanitized string.
End With
End Sub |
The user ID (UID) will change when source is exported by different users, but is not really needed when using a trusted connection. I think the add-in should strip out the UID value when it finds
Trusted_Connection=Yes. This will help reduce source changes when multiple users are working on the same project."Connect": "ODBC;Description=MyProgram;DRIVER=SQL Server;SERVER=DbServer;UID=johndoe;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=MySQLDatabase"This should be pretty easy to do with linked tables, and slightly more difficult with pass-through queries because the connection string line will need to be parsed and reconstructed.
The text was updated successfully, but these errors were encountered: