Author Archives: Mikhail

GA of K2 5.2 – time to try new version

Today 17.10.2018 K2 5.2 went into GA stage meaning news about release were sent to all clients and partners and starting from now we can download this new and shiny version from K2 portal. So it is a perfect time to do a little review. Without further ado let me start with this.

You can download 5.2 installer from K2 portal. And providing you have test VM with current version of K2, update manager will get you to new version  withing 30 minutes or so. Once installer completes extraction of files you presented with splash screen:

Splash screen provides you with essential information (.NET 4.6.1 requirement, where to run an so on) and allows you to kick off installation process (conservative people like me can still locate Setup.exe and run it from Installation folder).

In case of existing installation detected K2 Update manager detects that and gets you upgraded just in few steps:

In case you run with multiple security labels you will immediately notice improved label selection UI which is no longer looks like something from the past and fully aligned with modern K2 UI design:

Additionally you will notice increased number of available OAuth resource types:

My favorite under the hood improvement, which is really huge thing, is completely rebuilt identity cache and sync architecture which was brought into on-prem product from its cloud version (if I employ Microsoft-speak “battle-tested in the cloud” and so on). At this stage all the internal infrastructure of new Sync Engine is already here in 5.2 RTM, yet it is disabled – stay tuned for official news for when this feature will go live for all customers. At initial stage K2 will work with selected customers to assist them to enable and transition to the new Sync Engine. But like I said, you already can see that underlying infrastructure for New Sync engine is already here in 5.2 release. In case you familiar with back end/underlying tables you can tell that number of Identity tables has increased:

And Identity.Identity table has been expanded too:

Long story short with all these changes and new sync engine enabled your Identity cache sync speed will be greatly improved and, for example, even your URM Get Users SmO call against Azure AD label can be served from cache without doing query to AAD.

There is more improvements and new features and I will try to cover them in greater details a bit later.

Additional resources / next steps:

Download K2 5.2

5.2 Release Notes

5.2 User Guide

5.2 Installation and Configuration Guide aka ICG

K2 5.2 Developer Reference aka DevRef

K2 Five (5.2) Fix Packs

Facebooktwittergoogle_plusredditpinterestlinkedinmail

K2 and AAD manual integration configuration – errors and solutions

Even after doing 3-5 installations which leverage manual integration between K2 and active directory I keep bumping into errors which at times take disproportionally large amount of time to decipher them and pin point that tiny/silly error in configuration settings which prevents your setup from working. So I decided to collate them all into the “symptom-solution list” and keep in one place – i.e. in this blog post.

AADSTS50011: The reply url specified in the request does not match the reply urls configured for the application


AADSTS50011

That most likely means that Token Endpoint Reply URL is not specified in your AAD app properties. That URL should look as follows https://{K2SiteURL}/identity/token/oauth/2 and you need to make sure that it is added in your app Settings.  You do that in Azure Portal > Azure Active Directory > App Registrations > %Your_K2_App_Name% > Settings > Reply URLs. 

Add required URL and be sure to wait something like 30 seconds at least after applying this change and try logging in again.

AADSTS700016: Application with identifier ‘%APP_URL%’ was not found in the directory ‘%AZURE_DIRECTORY_ID%’.

AADSTS700016

dThis error message means either mismatch between identifierUris listed in your app manifest file and URL reported in error message or absence of these URLs in app manifest. Your K2 AAD app manifest file should contain your Runtime and Designer URLs, as shown on the screenshot below:

One thing to remember here is that when you edit App ID URI from AAD app properties and put updated value there it overwrites your identifierUris list in app manifest values – it removes your Designer and Runtime URLs from there (and anything else listed there) and puts updated App ID URI value there, which will give you  AADSTS700016 error.

Claim mapping configuration cannot be found for this claim. Claim information: Name=”

This was the one I wasted hours of troubleshooting time triple-checking all my configuration and asking each and everyone to help me spot what is wrong with my set up, only to discover that I tried to perform logon with my Azure tenant admin account which was listed in AAD users list as account with “Microsoft Account” source whereas it is necessary to create a user in AAD (all of those listed in AAD Users list with “Azure Active Directory” specified in Source column). I’m not 100% sure if this problem can be better handled on K2 side to present more actionable/clear error message – one we have here nudge you into direction of checking identity claim mapping while in this scenario problem is completely different.

I will be extending this list with other error messages as I encounter them.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Unable to run ConnectionStringEditor.exe – “Did you run the tool from the K2HostServer directory?”

Sometimes you may get quite strange errors with simple solutions and silly reasons. Here is an example – you get a complaint that K2 ConnectionStringEditor.exe cannot be started with the following error message:

And that may really confuse you, especially when you are sure that it is clean, new and shiny, correctly performed installation 🙂 You start wondering what cofig is required and why on earth it had disappeared when there are seemingly no people tend to kill configs during the log clean up nearby. But what you need here before go into panic mode is second look at larger screenshot maybe… like this one:

So the error is actually caused by attempt to run the tool from search results instead of doing that by locating it in its default location (“C:\Program Files (x86)\K2 blackpearl\Host Server\Bin\ConnectionStringEditor.exe”).

Another quite typical error with this utility is attempt to run it without elevation on a server which has UAC enabled (I assume it should be any production Windows server, but you may see it disabled in some environments still).

Facebooktwittergoogle_plusredditpinterestlinkedinmail

K2 Configuration Analysis – Could not create certificate: Keyset does not exist

I was doing K2 5.1 installation recently and bumped into the following error reported by K2 installer:

The following was recorded in installer trace:

>> Certificates.FindCertificate: Find cert start
>> Certificates.FindCertificate: Find certificate: D9F8C4EF74BD33B6C9FCA421E68222E77B1A3315 in LocalMachine – My
>> Certificates.FindCertificate: Found 1 certificates
>> Certificates.ExportCertToString: Exporting…
>> EnsureCertificate.Execute: Logged Error: Could not create certificate: System.Security.Cryptography.CryptographicException: Keyset does not exist

at System.Security.Cryptography.CryptographicException.ThrowCryptographicException(Int32 hr)
at System.Security.Cryptography.X509Certificates.X509Utils._ExportCertificatesToBlob(SafeCertStoreHandle safeCertStoreHandle, X509ContentType contentType, IntPtr password)
at System.Security.Cryptography.X509Certificates.X509Certificate.ExportHelper(X509ContentType contentType, Object password)
at SourceCode.Install.Security.Certificates.ExportCertToString(X509Certificate2 cert, String password)
at SourceCode.Install.Package.Actions.Security.EnsureCertificate.Execute(Target target)
>> EnsureCertificate.Execute: End EnsureCertificate, success: False
>> Target.Execute: !Completed: Server – EnsureCertificate, Success: False

It was really strange as everything was working fine and seemingly all required certificates were in place. I tried to check on “C:\ProgramData\Microsoft\Crypto\RSA” folder permissions but they were seemingly OK and updating/editing them didn’t resolve K2 installer error for me. Though as I later found out I was looking into the right direction. I next rebooted the server a few times and eventually lost RDP connectivity to it with the following error message displayed by MSTSC client:

At this point it was took over by Windows server admin who resolved MSTSC error after stumbling on the blog post at Ask the Performance Team Blog and correcting RSA folder permissions. As I understand final solution was… …drum roll… ensuring/fixing correct permissions on “C:\ProgramData\Microsoft\Crypto\RSA” folder. Specifically NT AUTHORITY\NETWORK SERVICE account didn’t have rights on “C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys” folder. Once permissions were adjusted it fixed both MSTSC connectivity and K2 installer error mentioned above.

My takeaways from this case and aforementioned blog post are the following:

– For RDP problem there was correlating server side even with ID 36870: A fatal error occurred when attempting to access the TLS server credential private key. The error code returned from the cryptographic module is 0x8009030D. The internal error state is 10001.

– In general if I’ll see this error next time I will run Procmon while reproducing this error/running installer with expectation that it will show an “Access Denied” error while trying to access some of the MachineKeys, e.g.:

“C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\f686aace6942fb7f7ceb231212eef4a4”

Once we have access denied error spotted it should be possible to correct permissions with certainty that problem is indeed caused by them. As we had certificate GUID mentioned in K2 installer trace I assume there should be corresponding access denied error related with accessing this specific machine key which can be caught with help of Procmon utility.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

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.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

MSTSC unable to connect with “CredSSP encryption oracle remediation” message

Just run into this “CredSSP encryption oracle remediation” error message while trying to connect to Windows Server 2016 from Windows 10 client today. This issue is explained in MSFT KB article – “CredSSP encryption oracle remediation” error when RDP to a Windows VM in Azure and it all boils down to specific updates missing either on the server or on the client. In my case I was sure that my client box was fully updated and it was un-patched server outside of my control which was a culprit. For this scenario workaround is to set  Encryption Oracle Remediation policy to Enabled, and then change Protection Level to Vulnerable. Fastest way to do it on standalone box is to add registry key, which we can do using REG ADD command:

For workaround which works in un-patched client connecting to patched server scenarion refer to aforementioned MSFT KB article.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

SQL Server’s Binary Collations

I was reading up on database engine Always Encrypted feature while preparing for 70-473 exam, and bump into these binary collations which I somehow never heard of before.

While configure Always Encrypted one of the choices you have to made is whether to use Deterministic or Randomized encryption (and you have to know the differences very well for the exam). One of the caveats when using Deterministic encryption is that it have to use a column collation with a binary2 sort order for character columns. More specifically documentation states that: Deterministic encryption requires a column to have one of the binary2 collations. If you will be using SSMS Encrypt Columns wizard it will be converting your column collation into binary2 case sensitive collation.

These statement required me to investigate the topic of binary collation a little bit.

First of all you may have different collation settings on a SQL Server instance level (i.e. on its system databases) on your databases and on specific columns and expression level.

To list all the collations available on your instance of SQL Server you can issue the following SQL statement:

On Azure SQL Database you will get back 3955 rows or possible collations. Understanding collation requires you to understand set of related terms, such as Collation, Locale, Code page, Sort order.  You should also know  that there are three major sets of collations available to you:

  • Windows collations
  • Binary collations
  • SQL Server collations

These collation groups sort data differently. In the past my standard answer/explanation about Windows VS SQL collation was that Windows one is more frequently updated, more compatible and hence more preferable over SQL one. Technically speaking it is more about how sorting works, but as per MSFT documentation: “SQL Server supports supports a limited number (<80) of collations called SQL Server collations which were developed before SQL Server supported Windows collations. SQL Server collations are still supported for backward compatibility, but should not be used for new development work.” So what I was saying/writing seems to be correct.

I won’t be covering all the details and differences related to these sets of collations as I only want to focus here on Binary collations which are requirement for Always Encrypted Deterministic encryption.

Binary collations sort data based on the sequence of coded values that are defined by the locale and data type. They are case sensitive. A binary collation in SQL Server defines the locale and the ANSI code page that is used. This enforces a binary sort order. Because they are relatively simple, binary collations help improve application performance. For non-Unicode data types, data comparisons are based on the code points that are defined in the ANSI code page. For Unicode data types, data comparisons are based on the Unicode code points. For binary collations on Unicode data types, the locale is not considered in data sorts. For example, Latin_1_General_BIN and Japanese_BIN yield identical sorting results when they are used on Unicode data.

There are two types of binary collations in SQL Server; the older BIN collations and the newer BIN2 collations. In a BIN2 collation all characters are sorted according to their code points. In a BIN collation only the first character is sorted according to the code point, and remaining characters are sorted according to their byte values. (Because the Intel platform is a little endian architecture, Unicode code characters are always stored byte-swapped.)

So for  Always Encrypted Deterministic encryption any of the collations returned by the query below will do:

This leaves you with 133 collations to choose from 🙂 Generally speaking BIN and BIN2 collations use different sorting algorithms and BIN2 is more preferable in general, not only for Always Encrypted Deterministic encryption columns. Another interesting question is why we have BIN/BIN2 collations for different languages? Like Arabic_BIN2, French_BIN2 etc. The reason is that each of those uses different code page for encoding the characters sorting in the varchar type so linguistic collation type is very important and comes into play only for varchar data as this will be sorted based on the language selected (this is not applicable to nvarchar where it has no difference).

All collations which are not binary collation are linguistic collations. For example, Latin1_General_CI_AS is a linguistic collation and it uses a sorting algorithm compatible with several of English language and many Western European languages. Please don’t be confused by the name of Latin1_General, as it actually can sort all Unicode characters defined in Unicode 3.2 characters set and it can also sort many other languages correctly as well (if the language has no sorting conflict with the latin1_general sorting rule).

Binary collations have better performance than linguistic collations, and that is the main advantage of using then. A binary collation is always case sensitive and accent sensitive. BIN2 collation is generally more preferable than BIN collation.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

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

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Un mercenario al que pagas bien no te deja en la estacada

I haven’t been writing any language learning related blog posts for quite some time now. It is not because there is nothing to write about, on the contrary I have a lot of ideas big and small in language learning department, but I’m too busy with technology/work and other things.

Anyhow I’m very actively learn Spanish language and the moment, keeping on hold French and Afrikaans and postponing desire to learn other languages 🙂 I’m about to receive (unless I failed my exam) my DELE B1 certificate. Subjectively I can say that my writing capability still requires a lot of work as well as speaking lacks control of tense system though I can say a lot using limited amount of tenses and doing a lot of mistakes 🙂

My learning strategy includes loads of input from day-0 (listening, reading) and I’m currently reading “La carta esférica” by Arturo Pérez-Reverte and in this book I stumbled upon the following idiomatic expression – “dejar a alguien en la estacada”. Here is the passage from the book:

Además, siempre preferí contratar a asalariados eficientes antes que a voluntarios entusiastas… Un mercenario al que pagas bien no te deja en la estacada.

Pérez-Reverte, Arturo. La carta esférica (Spanish Edition) (Kindle Location 3386). Penguin Random House Grupo Editorial España. Kindle Edition.

So I decided to read up a bit on the phrase and below you may find what I learnt. Disclaimer: most of the post talks about etymologies of phrases/words and these are frequently contested, I  have to warn you that I didn’t do rigorous scientific check/verification and you are more than welcome do double check these theories. 🙂

I quickly found English equivalent for this expression – “to leave someone in the lurch”, and while meaning was clear both expressions required some extra checks in dictionary to understand where they came from. So basic modern meaning of both expressions is to abandon someone in difficult situation.

Let’s start from the Spanish one – “dejar a alguien en la estacada” if you are in a mood for definition of meaning in Spanish here you are – “La expresión ‘Dejar a alguien en la estacada’ es comúnmente utilizada para señalar cuando a una persona se la ha dejado abandonada a su suerte en una situación que podría ser peligrosa, apurada o de difícil solución, no brindándole la ayuda o auxilio que precisa” (source). But what is this “estacada” where our troubled person left? It actually comes from medieval jousting tournaments, martial game based on the military use of the lance by heavy cavalry.

Tournament between Henry II and Lorges, 16th century

Tournament between Henry II and Lorges, 16th century

Tournament field for this competition was fenced by “estacas” – wooden posts which formed sort of palisade and land within this fence was called “estacada” (tournament’s arena sort of). During tournaments, after competition was over only knight which felt from his horse (often heavily wounded) left on that land and victorious knight used to leave arena without helping or paying attention to one which stayed on the field. From this takes origin phrase “dejar a alguien en la estacada” which in modern language used to refer to “leaving someone in difficult or dangerous situation”.

What about English version? As you can see Spanish idiom has rather military origin and its English equivalent despite having similar meaning in modern usage has completely different origins. It also revolves around of the place where you left the troubled person – “lurch”. And honestly I had to look it up as I haven’t had an idea about what it could be. Dictionaries list number of meanings of which, knowing sense of the phrase, you may guess that one which we have in the phrase “to leave someone in the lurch” is this:

“a decisive defeat in which an opponent wins a game by more than double the defeated player’s score especially in cribbage

I also found interesting blog post which offers more interesting and fitting options for origins of lurch, such as:

1. Lurch is a noun that originated from lich – the Old English word for corpse. Lych-gates were the roofed churchyard entrances that adjoin many old English churches and are the appointed place for coffins to be left when waiting for the clergyman to arrive to conduct a funeral service. Hence ‘left in the lych/lurch’ supposed to mean “left in a quite difficult situation”…

Lychgate at the Church of St. James the Less, Philadelphia

Lychgate at the Church of St. James the Less, Philadelphia

2. Second theory states that jilted brides would be ‘left in the lurch’ when the errant bridegroom failed to appear for a wedding.

Those two seems to be apt/interesting yet only listed as suggested explanations with no evidence to support them.

And while most of the dictionaries link the lurch with losing/bad situation in cribbage aforementioned blog post mentioned above suggests that word/phrase “originates from the French board game of lourche or lurch, which was similar to backgammon and was last played in the 17th century (the rules having now been lost). Players suffered a lurch if they were left in a hopeless position from which they couldn’t win the game.” But again, looking at illustration they have there game board looks similar to the one for cribbage.

And after looking at both Spanish and English idioms which convey one idea yet have different origins I realized that both cribbage board and jousting tournament field have something in common…

Modern 120-hole cribbage board

Modern 120-hole cribbage board

Giovanni Ferri, Saracen joust in Piazza Navona in the 25th of February 1634 (Seventeenth century)

Giovanni Ferri, Saracen joust in Piazza Navona in the 25th of February 1634 (Seventeenth century)

Don’t you think?

Russian version anyone? If you interested in a Russian equivalent of “dejar a alguien en la estacada” / “leave someone in the lurch” I think it will be “бросить на произвол судьбы”, phrase which literal translation goes as “to leave someone to the arbitrariness of fate”… As you can see yet again completely different phrase to convey the same idea. Russian phrase centered around “fate” which is blind and not in a sense of unbiased Themis (known to Russian speakers as “Фемида” [Femida] and aka Justitia aka Lady Justice), but rather blind in its cruel arbitrariness. So to leave on  to the arbitrariness of fate would be leaving vulnerable person in really difficult situation.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

How to: Connect to Azure SQL from Visual Studio

This specific day is already described by some people as GDPRmageddon based on amount of emails people receive from all companies they ever dealt with at one or another point about their policies updates and so on. I’m not going to talk about this today, instead I decided to write this tiny post on how to connect to Azure SQL from Visual Studio.

Actually short answer to this is just fire off Visual Studio and select View > SQL Server Object Explorer, the rest is just “follow the wizard” thing, and it is all documented by MSFT of course. But I’m quite well aware about widespread allergy to official documentation (no matter how good it is) and also wanted to try this my self for the very first time recently, and this is how this post came about.

QUESTION: How to connect to Azure SQL from Visual Studio?

ANSWER:

1) First things first. Assuming you don’t have Visual Studio installed on your machine, you should download and install it. There is a free version named Visual Studio Community which can be downloaded here: https://www.visualstudio.com/vs/community

2) Once tiny web installer downloaded run it and click continue to kick off installation process:

Tiny installer will start fetching data from the internet before installing components on your  machine:

3) Once data downloaded you can opt in for default installation which will require 597 MB of disk space:

4) Once installation is complete Visual Studio will suggest you Sign up or Sign In to use additional services, but you can avoid that by clicking “Not now, maybe later” link:

5) Of course you must select Dark theme otherwise nothing will work and click on Start Visual Studio button 🙂

Of course other themes work too but you would look suspiciously in the developers crowd 🙂

6) Once Visual Studio opened you supposed to go to View > SQL Server Object Explorer, and… And if you followed previous steps exactly/selected default Visual Studio installation configuration SQL Server Object Explorer won’t be available in View menu:

7) To address this, re-run Visual Studio installer, open Individual components tab and mark SQL Server Data Tools component – it will automatically select bunch of dependencies upping disk space requirements from 597 MB to 2,52 GB:

8) Once installation complete, re-run Visual Studio and click SQL Server Object Explorer from View menu:

9) Once it opened, right click on SQL Server node in SQL Server Object Explorer tree and select “Add SQL Server…” option:

10) It will start Connect wizard where you can specify your Azure SQL server name and credentials (yes even for Azure SQL database you are connecting through “SQL server” which is logical entity you must have to connect to Azure SQL database(s) and should not be confused with SQL Server in Azure VM 🙂 ):

 

11) Both SSMS and Visual Studio are smart enough to detect if your server name and credentials are good but you don’t have firewall rule created to allow connection, and if you provide your Azure credentials these applications will be able to create required firewall rule for you:

To provide credentials you supposed to click on “Add an account…” under Azure account as shown above. It will open standard Microsoft credentials prompt dialog:

After you provided valid credentials you should be able to click OK in the previous window to create firewall rule:

If you navigate to Azure portal you will be able to see your public IP added to Azure SQL server firewall rules there.

12) And with valid credentials and firewall rule in place you will be able to connect and browse through your databases and objects and execute SQL queries more or less in the same way as in SSMS:

I hope now you are clear on how to connect to Azure SQL from Visual Studio 🙂 But in case you still have any questions just leave them in comments.

Facebooktwittergoogle_plusredditpinterestlinkedinmail