Errors for System Database References in Visual Studio

 System Database References in Visual Studio

Just a quick post for something I always forget with SQL Server Database projects in Visual Studio.

If any of the objects in the project refer to system views then building the project results in errors along the line of

SQL71501: View: [etl].[GetColumnTypeDifferences] has an unresolved reference to object [sys].[types]

The fix is to add a Database Reference in Visual Studio to the relevant system databases, usually just master but could also be msdb depending on what's being referenced. Right click References and choose Add Database Reference, select the required system database then click OK. Generally the other defaults won't need to be changed

BCP Export and Text Qualified Columns

 Bcp is a pretty efficient way of doing a simple extract of a table or query to a delimited flat file, something which still seems a pretty common task even in today's world of fancy ELTL tools and processes, though it does have it's quirks and limitations.

One of these limitations is that there's no easy way to text qualify string values in columns for cases where the file delimiter might be a valid character in the column value. If there's flexibility around the allowable column delimiter then this isn't too much of a problem, but sometimes the value for the delimiter is set by an 3rd party system or process.

About the Code

This PowerShell script will take an array of tables, determine the column metadata, text qualify and string columns with double quotation marks, and then extract the data to a specified flat file. 

If you want a different text qualifier then change the text "CHAR(34)" in the column query to the qualifier you need. This also uses a pipe delimiter, but this can be changed in the $params variable by altering the value "-t|" to the value you need. These could both be parameters to the script as well.

Parameters

For the parameters:

• $Tables is a string array of the tables to extract. It needs to be an array but could just be a single value if only 1 table needs to be extracted
• $Server is the SQL Server host and instance name if a named instance
• $Database is the database where the table lives
• $OutPath is the folder to extract the files to. The files will have the same name as the table, and a .txt extension. The extension could also be paramaterised or just manually changed on line 42
• $Schema is the schema of the table, default is dbo

 The Script

param(

[Parameter(mandatory=$true)]

[string[]]$Tables,

[Parameter(mandatory=$true)]

$Server,

[Parameter(mandatory=$true)]

$Database,

[Parameter(mandatory=$true)]

$OutPath,

[Parameter(mandatory=$false)]

$Schema = "dbo"

)

$bcp = "bcp.exe"

if(!(Test-Path -Path $outPath))

{

throw "Output path doesn't exist"

exit -1

}

 

$tm_start = (Get-Date) # Just used to get extract timings

foreach($t in $Tables)

{

[string]$columnQuery = "select stuff((

select ',' + case when system_type_name like '%char%' then 

                     'QUOTENAME(' + name + ', CHAR(34))'

when system_type_name = 'geometry' then  

                    '[' + name + '].STAsText() as ' + '[' + name + '_wkt]'

else QUOTENAME(name) end as [text()]

from sys.dm_exec_describe_first_result_set ('select * from $Schema.$t', null, 0)

for xml path (''), type).value('.[1]', 'nvarchar(max)')

,1, 1, '')

as collist"

$metadata = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $ColumnQuery

    

$query = "select {0} from {1}.{2}" -f $metadata.collist, $Schema, $t

    #write-host $query

$outFile = [System.IO.Path]::Combine($OutPath, $("$t.txt"))

$params = "`"$query`"", "queryout", "`"$outFile`"", "-S", $Server, "-d", $Database, "-T", "-c","-t|", "-C", "65001"

#Write-Host $params

$b = & $bcp $params

#$b

}

 

# Extract time

$tm_end = Get-Date

[timespan]$span = $tm_end - $tm_start

$span.Seconds

Powershell Scripts and Passwords

 One of the struggles I have with automating things with PowerShell is securely managing the passwords. Using Windows credentials from the service/automation account is probably the best option, but not always possible so the question becomes how to securely store the password somewhere that the script can use it but it's generally secure from anyone discovering the script.

The  most practical way I've discovered so far is to encrypt the password using the account that will run the automation on the server where the automation will run, and then save this encrypted password in a config file that the script will read. For jobs run by the SQL Agent this would mean encrypting the password using the SQL Agent service account on the SQL Server.

Encrypting the Password

The password gets encrypted using the following basic format

Read-Host | ConvertTo-SeccureString -AsPlainText -Force | ConvertFrom-SecureString

instead of using Read-Host to read the string from the console you can also hard code the password into the command, e.g.

'password' | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString

or read the password into a variable and then convert, e.g.

$pwd = Get-Content c:\temp\test.txt
$pwd | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString

The resulting long string of gibberish can be added into a file to be read in by the actual script at runtime.

Decrypting the Password

In order to use the password it has to be decrypted by the same user on the same machine as where the encryption was done. Using a different user or different machine will result in the password not being decrypted correctly.

Decrypting is a bit more complex, as follows

$password = (Get-Content $SecretFile) | ConvertTo-SecureString
$decrypted = [System.Runtime.InteropServices.Marshal]::PtrToStringBSTR([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($password))

The first line reads in the encrypted password from the path in the variable $SecretFile, e.g. c:\temp\test_encrypted.txt and converts it back into a PowerShell SecureString object.

The next line uses the .Net Marshal class functions to convert the secure string object back into plain text.

Final Thoughts

This isn't perfect, as anyone that can access the server and user can decrypt the password so maintaining good security around admin permissions on servers and well protected service accounts is a must. But it's far better than having plain text passwords lying around in scripts.