Sql-Server

At one of my customer sites, I recently started having an issue logging onto an Azure SQL Database. The customer had configured Azure Active Directory (AAD) and were using Multi-factor Authentication (MFA).

I had previously been using it OK.

I would connect to the server using SSMS, enter my username and password, and then be prompted for the MFA authorization. After I authorized the logon, I would then receive the error shown in the main image: Login failed for user ‘NT AUTHORITY\ANONYMOUS’ and Error 18456.

2019-09-25

I previously mentioned that SQL Server supports a wide variety of data types. The first of these are what we would consider exact numbers. Here are the available types:

So there’s a tinyint that’s basically one byte in size. One byte (8 bits) gives us 2^8 possible values, so we have 256 possible values. The first one is zero, so we have values up to 255.

Note: tinyint doesn’t allow for negative values.

2019-09-23

Authentication in Snowflake is based around user identities. That’s very similar to what we have today with SQL Server authentication (i.e. SQL Server logs you on), as opposed to Windows Active Directory authentication (i.e. Windows AD logs you on), or  Azure Active Directory authentication (i.e. AAD logs you on).

I can create users if I’m a member of either the SECURITYADMIN or ACCOUNTADMIN roles.

Snowflake has a single specific password policy:

2019-09-20

Every so often I hear a discussion start about whether ID should be used with both letters capitalized, or whether it should be Id.

The argument from the Id proponents is pretty simple. Id is short for identifier and when we use other acronyms, we normally just capitalize the first letter from each word.

I understand that argument and I see some merit in it. However, that’s not the way I use it.

2019-09-19

When I’ve reviewing customer databases, I often come across tables that have been created by users, yet they are completely empty. Now that might be perfectly OK but sometimes, it’s a mistake that should be cleaned up.

So, in our free SDU Tools for developers and DBAs, we added a tool that can look for user tables that are empty. No surprise, it’s called ListEmptyTables.

It takes three parameters:

@DatabaseName sysname - the database to look into @SchemasToList nvarchar(max) - a comma-delimited list of schemas to check (or ‘ALL’) @TablesToList nvarchar(max) - a comma-delimited list of tables to check (or ‘ALL’)

2019-09-18

When you work with Azure SQL Database, you’ll quickly learn that you send queries to a single database, and you can’t execute USE statements to change to another database. Importantly, you also can’t use four-part names to access objects in other databases like this:

ServerName.DatabaseName.SchemaName.ObjectName

So it’s not surprising that when I’ve been reading some questions in Stack Overflow, asking how on earth you can join the list of users in your database, to the list of logins in the master database. And less surprising that the answer normally is that you can’t do that, and you need to connect to each separately. However, that’s not true. You can get to them using external tables. Let me show you how:

2019-09-17

When you store data in a database,  individual values are stored in columns. Now columns have 2 basic characteristics:

• Data type
• Nullability

Another common characteristic is:

• Size (can be maximum length, precision, scale, etc.)

The first of these, the data type of the column, determines what types of value can be stored in the column. For example, I might have an email address and I might decide that that is a string of characters, but I might also have a price and might decide that that needs to be a number of some type.

2019-09-16

Security in Snowflake is quite different to what’s implemented in SQL Server.

In SQL Server, objects have owners that can either be specific people, or as occurs when a dbo person creates an object, it is owned by the dbo role. In Snowflake, objects in the database also have owners, but the owners can’t be users, they are always roles.

When you create a database, there are four pre-defined roles:

2019-09-13

I wrote last week about how I don’t like unnecessary abbreviations. It’s all part of a desire to see higher quality code, because that costs less to maintain and support.

Another key issue that troubles me in development is when I see inconsistency.

Single Brain

When you have a team of people doing work, you need to endlessly try to make the outcome look like it’s come from a single brain. And equally importantly, make it look like it’s come from a single brain that cares about quality and attention to detail.

2019-09-12

Every now and again, I need to store IP address values in T-SQL and I want to check if the string that I’m passed is a valid IP address. So, in our free SDU Tools for developers and DBAs, we added a simple tool that works that out. It’s called IsIPv4Address.

You can tell by the name that it only works with IPv4 addresses, not IPv6.

Nothing complex. It takes a string, checks the format, and the range of octet values, and returns its verdict.

2019-09-11