MS SQL Azure – Creating contained users – SQL Authentication

In every database engine it is important to create logins that enforce security around your database and that can be maintained.
Additionally if you are working for a client you may wish to transfer this database at some point in the future to the client.

In SQL Azure users can be created against the master database in the instance and the role can then be transferred to individual databases.

Fine but if you wish to transfer the database the roles will be compromised and error when the database is moved.
One suggestion to avoid this issue is to use contained users.

Using your sys admin account connect to the database and run;

CREATE USER rocketengineapplication WITH PASSWORD = 'bluedanube';
ALTER ROLE db_owner ADD MEMBER rocketengineapplication;

Now developers could use this password and username to login to the database and do most of what is required without having any privileges to the SQL Server and if you ever transfer the database the role will pass with the database.

Here is a link to built in database roles
SQL Database Roles

Secure a single or pooled database in SQL Azure

and here is a useful query that can be run to identify the users and roles that a particular database has. This allows you to check what users are on a database and what are there roles.

SELECT u.name AS UserName, u.type_desc AS UserType, r.name AS RoleName
FROM sys.database_principals AS u
LEFT JOIN sys.database_role_members AS rm ON rm.member_principal_id = u.principal_id
LEFT JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id
WHERE
    u.type NOT IN('R', 'G')
ORDER BY
      UserName
    , RoleName;

Upload Shape Files into SQL Azure using OGR2OGR – Explanation of SQL Azure Connection String to be placed within OGR2OGR Command Line

Lets say you have a SQL Azure Server with the following parameters

SQL Azure Instance : azureinstance1
Database name within Instance : TouristDB1
Your User Name is : tom
Password is : Edinburgh

The SQLAzure connection string would be
MSSQL:Server=tcp:azureinstance1.database.windows.net;Database=TouristDB1;
Uid=tom@azureinstance1.database.windows.net;Pwd=Edinburgh;

and the full OGR2OGR to import Command Line Instruction for a shape file called Monuments.shp would be..

ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:Server=tcp:azureinstance1.database.windows.net;Database=TouristDB1;
Uid=tom@azureinstance1.database.windows.net;Pwd=Edinburgh;" "C:\Monuments.shp"

Upload XLS information to a specific table or new table within an SQL Azure Database

I have started experimenting with Microsoft Azure if you haven’t already you can get a free experimentation account here;

Microsoft Azure Trial account with £125 credit

This gets your registered on Microsoft’s cloud and after a free trial period you will be able to continue with a Pay as You go Account which depending on the services that you go for can start at very cheap rates.

In order for this to work you will need the following
1) Microsoft Azure account
2) An SQL Azure Database
3) SQL Server Management Studio downloaded and installed on the machine you will be uploading from, this can be obtained from SSMS download link be warned its over 800mb. Here I use SSMS 2016
4) Know your server name this is generally [Yourname].database.windows.net
5) Login and Password (I use SQL Server Authentication)

Testing things out I have been using the Web Apps Service to run a website and connect to an SQL Azure Database – both on the cheapest options.

What makes the website particularly cheap is that it can be stopped and started and by paying for it by the minute you can really get a powerful demonstration sites up and running and stop them immediately after the demonstration for very little money.

So after having created an Azure SQL database (Microsoft Create Azure Database Tutorial)I wanted to get a decent number of records into it. Which would be the starting position when taking on most work.
Here I use the Lichfield Planning Application information previously referred to in this post QGIS Import. What I did was take the 45,000 records of planning applications from the shape file. I did this by opening up the dbf file of the shape collection in Excel 2003 and then saving it in excel format. This will be used later to import into the database.

Having your excel file ready the next step is to open up SQL Server Management Studio and connect to your Azure Database. The parameters with regard to username and servername will have been setup when you created your Azure database it is important that when you create your Azure database you somehow record these details.

Next highlight the database – in my case this was DB001 and right click to get tasks.

At this point you enter the import wizard windows dialog boxes and having passed the opening welcome screen , a screen that can be turned off for subsequent navigations, you should hit your first screen that allows you to define the format of the file that should be imported.

The next step is about the only one that is slightly confusing – you are given a number of different options for the target – for me SQL Server Native Client 11.0 worked for me.

Now using the previous parameters specific to your database server and your database name complete the next dialog.

The next dialog asks you whether you want to copy all information or want to write a query to filter the information to be imported. For my example I chose the all import item. Here I select the database and then I am able to see the from and too destinations.

If you wish to import into an existing table use the drop down to select tables from the database – if you wish to import into a new table you can type in the name of the new table within the square brackets.

Here I create a new table called T010Test and import and then continue through the import wizard dialogs until on completion of import you should see a similar screen to that below. It is possible to go into the edit mappings if you are copying into a table that already exists. This will give you a preview showing to what extent the mapping will be successful and how the fields map. You may wish to alter the names of columns to match your target table at this point.

Setting up a Blank SQL Server Spatially enabled Table using Microsoft SQL Server Management Studio 2008R2 Express and displaying it in QGIS 2.8.1

Programs used;

1- SQL Server 2008R2 Express
2- SQL Server Management Studio 2008R2 Express
3- QGIS

The example uses UK national grids coordinates to create a Triangle Polygon in a SQL Server Table

I’ve previously written that while we’ve had spatially enabled SQL Server for over 5 years I constantly come across line of business applications that although using SQL Server have not and do not intend to spatially enable the application. This is undoubtedly because of the difficulty in re-designing legacy systems actively in use and because the benefits although significant are not generally requested by all but the most knowledgable of colleagues.

While I understand this legacy system reasoning spatially enabled databases are the future so its just a matter of when and not if an application will require alteration. Understanding it in this context makes it really a requirement to start seriously planning for its inclusion.

Developerers creating new applications however should always consider spatially enabling relevant tables from the start even if it is not specked by the client/colleague. It being so much easier to spend a couple of minutes future proofing the schema of a new born database rather than hours trying to retrofit a live in production back end.

Firstly it’s important to understand what a geodatabase in SQL Server actually is.
Really it is a normal database which has one table that has a field that has a geometry or geography value type. In this example I will use desktop QGIS 2.8.1 to display the resulting geometry but any other digital mapping package that can link to SQL Server could be used. SQL Server also has a very rudimentary Mapping Display but you will need something better if you want to manipulate boundaries visually.

Many digital mapping products have plugins that will create Geodatabases and tables however I haven’t seen one for QGIS. I really wanted to be able to create spatial SQL tables on my own without recourse to paid tools directly in SQL Server Management Studio. So here’s my method of creating blank polygon table whose geometry is ready to be read and edited in QGIS or any other digital mapping system just using SQL Server Management Studio Express 08R2.

Steps
1. Create a new Table
2. Ensure the table has an identity Key that increments
3. Create a geometry column
4. Write a query that updates the geometry column

UPDATE T001Sites SET Coordinates=geometry::STGeomFromText(‘POLYGON((301804 675762,295789 663732,309581 664870,301804 675762))’,27700)

You will note that there are four coordinates here (each coordinate being a pair of numbers )
The first coordinate and last are the same this is required by SQL to ensure that the polygon is closed.

The 27700 number is the Spatial Reference System Identifier (SRID) – it is a unique value used to unambiguosly identify projecttion. 27700 is the unique identifier for the United Kingdom National Grid and represents the coordinates my example refer to. The spatial reference identification system is defined by the European Petroleum Survey Group (EPSG) standard which is a set of standards developmed for cartography surveying etc and owned by the Oil and Gas Producers Group list here; http://www.epsg-registry.org/

The above coordinates display a triangle in West Lothian near Edinburgh

5. Set up the connection to SQL Server Instance

Ensure the box marked “Only look in the geometry_columns metadata table” checkbox is unchecked. By default this is checked and if the geometry_columns table does not exist you will get an error message.

QGIS-SSMS-Connection

6. Display the table and edit as appropriate.

Select the table then hit the Add button

QGIS-SSMS-TableReadyforDisplay

And here is the SQL Server table in QGIS ready to be added to or edited.
QGISshowingSQLServerPolygon

Attaching Databases to SQL Server 08R2 Express

jigsaw2
It should be noted that the following although the easiest way to get a new database into an instance it should not be used in a production environment. In fact doing so may get you sacked. If experimenting though this method should be fine. If in doubt seek help as in a production environment you would want to look through all the code before attaching anything into an instance.

Go to SQL server management studio and on the Databases tree
Right click and select
Attach…..

Attach database window should appear which will allow you to use the Add… button to navigate to the
\Data\ subdirectory where all the sql server databases are held.

IMPORTANT – prior to loading a file in the database will have needed to have been DETACHED and you should always move anything mdf file that you are wanting to put into a database into the data subdirectory.
This ordinarily is done by going to database in question scrolling down to the database and right clicking on the database

Tasks > – Detach…

WARNING
If a database has not been detached properly it may NOT be possible to re-attach the database this is of course a security feature. So experimenting with simple moving files about will not work…

The listed code below does it at command line but the above works in SSMS

The default location for databases in SQL 08R2 Express is
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA

The default location for database in SQL 2012 is
C:\Program Files\Microsoft SQL Server\MSSQL11.[InstanceName\MSSQL\DATA

Stopping and Starting SQL Server Instance 08R2 Express

startstop

Simply within SSMS right click on the instance and hit STOP and
To start an instance if you are still in SSMS you can simply right click and hit start

Note if you stop a SQL Server instance from within SSMS AND then exit SSMS and try and go back in you will be denied as SSMS is unable to connect to a service that is not up and running.

In such a case to restart the instance you need to go to SQL server configuration manager and click on the instance and restart from there.
In Windows 8.1 you can get to config manager by using the search facility.

Windows 10 Alternative
Hit the search ring
type in services.msc
A new dialog should appea that will have Services(Local) with several columns – Name / Description / Status / StartupType
You want the Status to be Running
Use the mouse to highlight SQL Server (MSSQLServer) and then right click
Start

Your server should now be running

Allowing sa login and altering password SSMS SQL 08 R2 Express

password-security

Recently I installed SQL Server 08 R2 Express.

As part of the standard installation the sa login is disabled as default.

Clearly although this is a useful security feature it is a bit awkward if you are wanting to undertake certain tasks and as part of the installation this security feature is in no way obvious.

So what do you do to re-enable it?

Firstly log into SSMS under the machine windows login.

Go to the Security section of the server (not any of the databases) and expand the Logins branch. SA the system admin should be listed and if it is disabled it will have a small red down arrow next to it.

To re-enable highlight sa and right click

Select properties

Select Status

now alter the following
Permissions to connect to database engine
Set this to GRANT

Login
Set this to Enabled

Now highlight instance itself and right click and go to properties.
Within server properties highlight Security.
And change set server authentication to
SQL Server and Windows Authentication mode.

You should have sa login now enabled – don’t worry if sa still has a small red arrow next to it the SSMS client needs to be refreshed to see changes to set up.

If you want to change the password for the SSMS server I would recommend running the following.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'DifficultPass9£' ;
GO

Note you can enforce the requirement for a strong password within the same security section of the sa login if you require.