Some time ago I wrote a blog post where I explained how to change SQL Server instance collation for installed SQL server instance (see “Changing SQL collation for deployed instance without reinstall (almost)“). That post contained some scripting bits to detect installed SQL Server version and navigate to appropriate setup directory to facilitate collation change process. Recently I had a bit of time to consolidate these bits of PowerShell into one script which detects installed SQL Server version and changes directory to appropriate setup folder. Here you have it:
Recently I bumped into a problem which was super obvious in retrospective, yet took me some time to untangle it. K2 environment was upgraded from 4.6.11 to 4.7 and K2 installation path was changed in the process (drive letter). After upgrade was completed without warnings or errors, we did some more testing and found that one of the forms which was using Oracle Service Instance based SmartObject started to throw an error similar to this one:
Could not load file or assembly – SourceCode.SmartObjects.Services.Oracle.dll
Essentially it was very clear from the error message that Oracle Service instance keep looking for related assembly in old installation location (wrong drive letter). We switched to SmartObjects Services Tool only to see that there we are unable to edit or create new service instance of this service type. At this point I looked at old cases mentioning similar error message and surprisingly large amount of them was proposing workarounds and things not quite related with the root cause. We spend some time addressing missing prerequisite for this service type – 64-bit Oracle Data Access Components (ODAC) version 2.121.2.0 or higher, which mentioned as such in 4.7 user guide (_) and checking some related settings and so on.
But I next paid attention to the fact that environment had 2 service type for Oracle one of them was working, while another one does not. I next dropped assembly mentioned in error message in old installation location and restarted K2 service – it then fixed first Oracle service instance, but broken another one – it started to say that assembly SourceCode.SmartObjects.Services.Oracle.dll has been already loaded from another location, and this brought my focus back to the real problem – somehow one of the Oracle service types was not updated by K2 Setup Manager to use new installation path. Probably it was somehow “custom” and somehow was skipped by installer because of that. Anyhow my next step was finding where this path is defined. As soon as I confirmed that I cannot see/edit Service Type definition XML from SmartObjects Services Tool I switched to K2 database to check it there.
Necessary word of warning: Backup your K2 database before attempting any direct manipulations in it, and make sure you understand what you are doing before starting doing that 🙂
Service type definitions live in the follow [SmartBroker].[ServiceType] table, so I located “problematic” service type to check on its XML which is stored in ServiceTypeXML column. Here is the sample query to quickly search for service instance definition based on its Display Name:
Than will return you XML column value, on which you can click to view it as a formatted XML, here is an example of how it looks like:
Service Type XML
As you can easily service type definition contains assembly path parameter in its XML. So now it is only a question of updating it with correct value. Here is sample script to do that:
That will iron out problem with misbehaving service type. I don’t think that it can be very frequent problem as normally installer updates all the assembly paths definition with new path. But, especially if you have some custom service type, you may want to scan your service types definitions for any vestiges of old installation path. Here is a sample script which will display all Service Instances definitions which contain old drive letter reference (my example uses “D:\%” as a search criteria):
I hope that this blog post may help someone who may bump into similar error in K2 and if not, then maybe you can make use of SQL script samples which use filtering based on values within XML columns.
P.S. Note that all scripts mentioned above are for K2 4.7. In K2 Five (5.x) structure of the [SmartBroker].[ServiceType] table has been changed – it no longer has XML column named [ServiceTypeXML] and assembly path is stored in dedicated text column [AssemblyLocation] instead.
Earlier I tried to compile list of Microsoft documentation and other resources relevant for 70-473 exam preparation but I quickly realized that exam scope makes this list too huge and unwieldy. I now decided that I would rather split this into smaller resources lists following specific exam sections. Below you can see the list of reading resources relevant for Design and Implement Security section of 70-473 exam. You will mainly find links to Microsoft documentation in this list.
Here you can see Design and Implement Security exam section topics as described on official exam page:
Design and implement SQL Server Database security
Configure firewalls; manage logins, users, and roles; assign permissions; configure auditing; configure Transparent Database Encryption (TDE); configure row-level security; configure data encryption; configure data masking; configure Always Encrypted
Design and implement Azure SQL Database security
Configure firewalls; manage logins, users, and roles; assign permissions; configure auditing; configure row-level security; configure data encryption; configure data masking; configure Always Encrypted, configure Automatic Threat Detection
I tried to structure list of links below based on sub-objectives.
TDE is a SQL Server feature which encrypts your data at rest, i.e. your database files. When TDE is enabled encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database. Here is TDE architecture schema from MSFT documentation:
Scenario 1. On-premise SQL Server 2017 (this will also work for SQL Server in a Azure VM). You can use the following SQL script to enable TDE:
Be sure to replace ‘K2’ with your target database name and adjust password value. Script uses IF clauses to avid creating things which already exist (which are missing in the sample script you can find in MSFT documentation). Once TDE is enabled you can confirm this in the database properties using SSMS GUI:
Scenario 2. Azure SQL Database. Script mentioned above won’t work here. Easiest/default approach to enable TDE for Azure SQL Database is to do so from Azure Portal:
This approach called service-managed transparent data encryption and by default database encryption key is protected by a built-in server certificate. All newly created SQL databases are encrypted by default by using service-managed transparent data encryption.
Other approach called Bring Your Own Key and requires use of Azure Key Vault.
TDE can also be managed with PowerShell, Transact-SQL and REST API. PowerShell contains number of cmdlets for that:
And using T-SQL you can use ALTER DATABASE (Azure SQL Database) SET ENCRYPTION ON/OFF command (encrypts or decrypts a database) and two dynamic management views:
databasesys.dm_database_encryption_keys which returns information about the encryption state of a database and its associated database encryption keys
sys.dm_pdw_nodes_database_encryption_keys which returns information about the encryption state of each data warehouse node and its associated database encryption keys
Once TDE has been enabled there is also options to check whether it is enabled or not using T-SQL:
For further information refer to official MSFT documentation:
