Converting all AD user properties fails

[jdhitsolutions]

This was originally reported in Issue #13

I can run

get-aduser artd | ConvertTo-MySQLiteDB -Path .\art.db -TableName user -TypeName aduser

with no errors. But it fails when using all user properties.

get-aduser artd -Properties * | ConvertTo-MySQLiteDB -Path .\art2.db -TableName user -TypeName aduser

Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error
near "-": syntax error"
At C:\Program Files\WindowsPowerShell\Modules\mySQLite\0.9.2\functions\New-MySQLiteDBTable.ps1:119 char:21
+                     [void]$cmd.ExecuteNonQuery()
+                     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SQLiteException

The database gets created but not the table.

I did a quick test and get the same error with groups but here I get it even with the default output.

PS C:\> get-adgroup sales | ConvertTo-MySQLiteDB -Path .\salesgroup.db -TableName group -force
Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error
near "group": syntax error"
At C:\Program Files\WindowsPowerShell\Modules\mySQLite\0.9.2\functions\New-MySQLiteDBTable.ps1:119 char:21
+                     [void]$cmd.ExecuteNonQuery()
+                     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SQLiteException

Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error
near "group": syntax error"
At C:\Program Files\WindowsPowerShell\Modules\mySQLite\0.9.2\functions\Invoke-MySQLiteQuery.ps1:95 char:25
+                         [void]$cmd.ExecuteNonQuery()
+                         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SQLiteException

[jdhitsolutions]

It looks like some AD Group property is failing to properly serialize.

[jdhitsolutions]

The ADGroup example failed because group looks like a SQLite key word. Using a different table name solved the problem.

get-adgroup sales -server dom1  | ConvertTo-MySQLiteDB d:\temp\sales2.db  -TableName grp

[jdhitsolutions]

But there may be an issue with property names that have dashes like msDS-User-Account-Control-Computed.

This works:

Get-ADUser artd -server dom1 -Properties Title,Description,Givenname,surname,Whencreated,WhenChanged,SAMAccountType,UserAccountControl,UserPrincipalName,lastlogon,lastlogondate,NTSecurityDescriptor,MemberOf | ConvertTo-MySQLiteDB d:\temp\art13.db  -TableName user -primary samaccountname  -force

But not when adding the dashed property.

Get-ADUser artd -server dom1 -Properties Title,Description,Givenname,surname,Whencreated,WhenChanged,SAMAccountType,UserAccountControl,UserPrincipalName,lastlogon,lastlogondate,NTSecurityDescriptor,MemberOf,msDS-User-Account-Control-Computed | ConvertTo-MySQLiteDB d:\temp\art33.db  -TableName user -primary samaccountname  -force

Then I get the logic error. And because I'm using database transactions that causes another problem.

[jdhitsolutions]

I'm learning more about databases all the time. Apparently, a hyphen is not allowed as a column name. I will need to detect it and modify the SQL query to insert it as [msDS-User-Account-Control-Computed].

[jdhitsolutions]

This appears to be fixed with the release of v0.10.0