Azure SQL Database Firewall Issue (Connectivity Issue)

Hey everyone! Yes, I'm still around and yes, this is still SQL Something!

I'm sounding like a broken record now but, as usual, life has been pretty busy. Definitely going to try to finish the year right however, by having at least one post for each of the last couple of months.

Now! Let's get on with it.

Today we are looking at connecting to a Azure SQL Database. In a nutshell, an Azure SQL Database is a DB that exists on a logical DB server, not an actual DB Server VM. It takes away the hassle of having to do server related admin tasks and allows you to focus on the DB itself and the data it contains.

For the purpose of this exercise, let's assume we created a Azure SQL DB and when we attempted to connect to it via Management Studio, we received the following:

Fig. 1: Danger Will Robinson!

Now, we can take the following steps:

• Ensure that you are using the correct server name, username and password.

If the above doesn't work, we will have to verify our Azure SQL Database Firewall rules.

• Log on to portal.azure.com with your valid user.
• Navigate to your azure SQL Databases.
• Click the database in question.
• Select 'Set Server Firewall'.

Fig. 2: Firewall awesomeness.

• Click add Client IP.

Fig. 3: Add yo IP

• Click 'Save'.

Now we can retry our connection.

And that's all there is to it. :-)

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: SQL Server Agent Job History

Hey all, welcome back to SQL Something. Man, has it been a while. Too long.

Unfortunate to say however, this post will not be particularly epic to make up for the long absence. Like, not epic at all. But, you know what? Let's go into it. If it helps one person, then that's plenty. :-)

Today we are looking at SQL Server Agent Job history.

A SQL Server Job allows us to automate a process to run at particular times, either as a one time event or repeatedly for some specified duration (or indefinitely). Setting up jobs for performing backups is an example of a great way to use jobs.

A job's history can show us a number of things. It can show us if the job ran successfully and if not, it can give us an indicator as to why. It can also show us some useful info such as the creds used to invoke the job, as well as the duration of time taken for the job to run (this last I find pretty helpful).

SQL Server Fixed Roles And Related Stored Procedures

Good day everyone and welcome back to SQL Something!

Do you like Roles? Do you want to know a little more about SQL Server's built in Roles and what they do? Do you want to know some nifty built in sprocs that also give some extra info about the Roles?

Then this is a post for you!


Firstly let us look briefly at SQL Server's nine (9) built in Roles:

• SysAdmin - This is the big guy. Members of this Role have all the permissions across the instance. A SysAdmin is specified during installation.
• ServerAdmin - Members of this Role are allowed to perform instance configuration tasks and can also stop an instance.
• SecurityAdmin - Members can alter and elevate permissions for Logins. Can elevate permissions to SysAdmin.
• ProcessAdmin - Members can stop instance processes.
• SetupAdmin - Members can add/remove DBs to linked servers.
• BulkAdmin - Members can bulk insert.
• DiskAdmin - Members can perform tasks involving instance related files.
• DBCreator - Members can add/drop/alter/restore databases on an instance.
• Public - The little guy. All logins are considered part of the public Role group. Not a 'real' built in role.

Next, we will examine a couple stored procedures that add to the above info.

Viewing Collation (Server/Database/Column) Via T-SQL

Hey everyone and welcome back to SQL Something!

Real quick one today and that is viewing Collation information via T-SQL (I will do a follow up post eventually on how to do it via Management Studio with pictures).

Now onto the T-SQL.

SSIS Error: Cannot Convert Between Unicode and Non-Unicode Data Types

Hellooo out there! Welcome back to (the not quite monthly) SQL Something!

This month we are looking at a conversion error I ran into when trying to alter an old SSIS package:

Fig. 1: Error!

Fig. 2: Change isn't easy...

Finally an error that's pretty self explanatory! The error says it can't convert from non-Unicode to Unicode, so obviously we need to find some way to make the two the same type. :-)

Using The LEAD() And/Or LAG() Functions

Heyyy, sliding in at the eleventh hour (as usual) we have the first SQL Something for 2016!
Hard to believe that I've actually managed to, sort of, maintain this for 3 years.

Anyhow, we end the year with a couple functions that follow a similar format to RANK(): LEAD()/LAG().

LEAD() and LAG() allow users to list the value of the next row or previous row respectively with some given offset from the current row.

Simple example time.