Tag Archives: SQL

PowerShell Script – Detect installed SQL Server Version and switch to appropriate setup directory

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:

Please follow and like us:
0

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: 

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.

Please follow and like us:
0

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

Database-Level Roles

Server-Level Roles

CREATE CREDENTIAL (Transact-SQL)

SQL Server Separation of Duties (Word document download)

Assign Permissions

Getting Started with Database Engine Permissions

New Permissions in SQL Server 2014: IMPERSONATE ANY LOGIN, SELECT ALL USER SECURABLES, CONNECT ANY DATABASE and the old CONTROL SERVER

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

Principals (Database Engine)

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

Row-Level Security

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

ALTER TABLE (Transact-SQL)

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

Always Encrypted: Protect sensitive data in SQL Database and store your encryption keys in Azure Key Vault

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

Controlling and granting database access to SQL Database and SQL Data Warehouse – Secure your Azure SQL Database

Azure Cosmos DB: SQL API getting started tutorial

Get started with Azure Table storage and the Azure Cosmos DB Table API using .NET

ADO.NET Overview

Securing your SQL Database

Azure Storage replication

Collation and Unicode Support

AzureRM.Sql

Management Data Warehouse

SQL Server Profiler

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.

Please follow and like us:
0

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

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:

Transparent Data Encryption (TDE)

The SQL Server Security Blog on TDE with FAQ

Please follow and like us:
0