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:
Tag Archives: SQL
Simplistic SQL cycle example
Just a little example (one of the simplest) demonstrating how to do a cycle in SQL to populate N rows in some table with specified value:
Just leaving it there 🙂
Unable to create new/edit existing Oracle Service Service Instance after changing K2 installation path
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:

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:

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.
Microsoft Azure 70-473 Design and Implement Cloud Data Platform Solutions – Design and Implement Security
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.
Configure Firewalls
Azure SQL Database and SQL Data Warehouse firewall rules
sp_set_database_firewall_rule (Azure SQL Database)
Azure SQL Database: Firewall security
Configure a Windows Firewall for Database Engine Access
Configure a Server to Listen on a Specific TCP Port
Configure the Windows Firewall to Allow SQL Server Access
TCP/IP Properties (IP Addresses Tab)
SQL Server: Frequently Used Ports
Security Considerations for SQL Server in Azure Virtual Machines
Manage logins, users and roles
Server and Database Roles in SQL Server
Managing Users, Roles, and Logins
Getting Started with Database Engine
CREATE CREDENTIAL (Transact-SQL)
SQL Server Separation of Duties (Word document download)
Assign Permissions
Getting Started with Database Engine Permissions
GRANT Server Permissions (Transact-SQL)
SQL Server Best Practices – Implementation of Database Object Schemas
+ see also:Â What are some best practices for using schemas in SQL Server?
Azure SQL Database and SQL Data Warehouse access control
Configure Auditing
Get started with SQL database auditing
Set-AzureRmSqlServerAuditingPolicy
Use-AzureRmSqlServerAuditingPolicy
Configure Transparent Database Encryption (TDE)
Transparent Data Encryption (TDE)
Transparent data encryption for SQL Database and Data WarehouseÂ
Enable TDE on SQL Server Using EKM
ALTER DATABASE (Azure SQL Database)
Configure Row-Level Security (RLS)
SQL Server 2016 : Implement Row Level Security using Predicate Function and Security Policy
SQL Server Security Blog – Row-Level Security block predicates are generally available on Azure SQL DatabaseCREATE SECURITY POLICY (Transact-SQL)
Configure Data Encryption
SQL Server 2016 New Features: Security and Encryption
Encrypt a Column of Data (column/cell level encryption)
Extensible Key Management Using Azure Key Vault (SQL Server)
CREATE ASYMMETRIC KEY (Transact-SQL)
SQL Server Certificates and Asymmetric Keys
OPEN SYMMETRIC KEY (Transact-SQL)
Get started with Azure Key Vault
About keys, secrets, and certificates
Configure Data Masking
SQL Database dynamic data masking
Use Dynamic Data Masking to obfuscate your sensitive data
Configure Always Encrypted
Always Encrypted (Database Engine)
Always Encrypted (client development)
Develop using Always Encrypted with .NET Framework Data Provider
Microsoft Azure SQL Database provides unparalleled data security in the cloud with Always Encrypted
Configure Always Encrypted using SQL Server Management Studio
SqlConnection.ConnectionString Property
Use .NET (C#) with Visual Studio to connect and query an Azure SQL database
Configure Automatic Threat Detection
Use PowerShell to configure SQL Database auditing and threat detection
Azure SQL Database Threat Detection
Azure Security Center Documentation
Other/General
Azure Cosmos DB: SQL API getting started tutorial
Get started with Azure Table storage and the Azure Cosmos DB Table API using .NET
Monitoring SQL Server Performance
Monitor Resource Usage (System Monitor)
Next time I will try to compile similar list for Design and implement high availability, disaster recovery, and scalability section of the exam.
How to enable TDE (SQL 2017/Azure SQL Database)
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:

Transparent Data Encryption Architecture
This blog post explains how to enable Transparent Data Encryption (TDE) for SQL Database (on-premise/Azure).
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: