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:
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 🙂
Recently I was doing installation of K2 5.2 on Azure VMs with SQL server named instance hosted on separate Azure VM. I’ve created SQL Server alias on K2 VM but then run into issue – neither K2 Setup Manager nor SSMS were able to connect to SQL through alias. I next tried direct connection via server\instance name which also failed. SSMS showed me the following error:
I first focused on network connectivity between VM:
- Confirmed that I can ping SQL Server VM from K2 Server VM
- Confirmed that no firewall enabled on VM and Azure VMs on the same network with nothing blocking connectivity between them
- I tried to use telnet to test port 1433 – it failed
This is what kept me focused on network connectivity layer a bit longer then necessary. But after confirming that SQL Server not listening on port 1433 using netstat -na | find “1433” it became quite clear that focus should be on SQL Server configuration. First of all – by default named instance listen on dynamic port, and you actually need to have SQL Server Browser Service enabled to ensure you can connect to named instance without specifying port while using dynamic ports. But in my case it was not that as in SQL Server configuration there was explicitly specified custom port (SQL Server Configuration Manager > Protocols for %INSTANCE_NAME% > TCP/IP Properties > TCP Dynamic Ports – if you have anything other than 0 in IPAll section fir this setting you are not using dynamic ports). When your problem is dynamic ports and disabled SQL Server Browser Service error message from SSMS looks as follows:
As you can see error message explicitly tells you “Error Locating Server/Instance Specified. To fix this either set 0 for TCP Dynamic Ports setting and enable SQL Server Browser Service or specify some port number there. You sort of choosing your dependency here – either browser service (may fail to start) or custom port (may be hijacked by other service). It seems that browser service is better approach.
So in my case I was confused by expecting named instance to listen on default port which was, to put it simply, wrong expectation. Here is how you can check on which port your instance is listening:
But obviously having access to SQL Server you can get this data from SQL Server Configuration manager too: SQL Server Configuration Manager > Protocols for %INSTANCE_NAME% > TCP/IP Properties. Just keep in mind that you need to check TCP Dynamic Ports value both for specific address and for IPAll section. But like I said in my case, the problem was not about ports. Once I found out instance port I noticed that I still cannot connect to it using telnet, just because IP address was not enabled in SQL Server Configuration Manager > Protocols for %INSTANCE_NAME% > TCP/IP Properties (meaning it had Enabled=0). I corrected that and telnet connectivity test succeeded.
Still, when I get back to SSMS I was getting the same error – “Could not open a connection to SQL Server. Microsoft SQL Server, Error: 53”. Reason? With SQL Server 2016 and latest versions of SQL Server, I keep forgetting that the latest and greatest version of SSMS still reads alias settings from x86 registry hive (meaning you need to configure SQL alias using cliconfg.exe from C:\Windows\SysWOW64) – I have a hard time getting use to it. Interestingly fully missing x86 alias triggers error message “Could not open a connection to SQL Server. Microsoft SQL Server, Error: 53” while one we configure with non existing server or instance name will give you “SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified”.
Anyhow your key takeaways from this post should be:
- Know your instance port
- Make sure that IP address is enabled
- We still need to configure alias twice (x86/x64) to avoid unpleasant surprises from apps reading setting from non-configured location
I hope this post may save some troubleshooting time for someone.
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.
Manage logins, users and roles
SQL Server Separation of Duties (Word document download)
Configure Transparent Database Encryption (TDE)
Configure Row-Level Security (RLS)
Configure Data Encryption
Encrypt a Column of Data (column/cell level encryption)
Configure Data Masking
Configure Always Encrypted
Configure Automatic Threat Detection
Next time I will try to compile similar list for Design and implement high availability, disaster recovery, and scalability section of the exam.
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.