Solving ‘Spatial types and functions are not available’ with Entity Framework

Mon, 27 Mar 2017 16:38:33 +0000

Using SQL Server’s Geospatial features with Entity Framework is awesome. I was crusing along just fine with using DbGeography for an ASP.Net application I’m working on… Right up until I deployed to the server. Yep. It worked on my machine, but alas, I was hitting a runtime exception on my test server.

This was the exception:

Spatial types and functions are not available for this provider because the assembly ‘Microsoft.SqlServer.Types’ version 10 or higher could not be found.

Luckily, I ran across a Stack Overflow answer that almost helped me get all the way there in solving this exception. It was actually the second-most up-voted answer that helped me the most. You’re welcome to reference these if you want, but since the info is scattered between the question and the two answers, I’m assembling it here for your convenience.

Fixing ‘Spatial types and functions are not available'

1 – Install the Microsoft.SqlServer.Types package from NuGet

You can install it using the NuGet Package Manager UI, or from the command line:

> Install-Package Microsoft.SqlServer.Types

2 – Follow readme.htm instructions

After you install the NuGet package, a readme.htm file is opened and displayed to you. If you don’t see it for some reason, a new SqlServerTypes folder was added to your project. You can expand it and double-click readme.htm to open it.

Follow the instructions within.

Depending on the type of app you’re deploying, you need to perform different steps. I happened to be deploying an ASP.Net MVC app, so I followed the instructions for adding a line of code to Global.asax.cs. Your situation may be different, but the Readme instructions are clear on what to do.

ASP.Net Web Applications

Open Global.asax.cs and add the following to the list of registrations in Application_Start():

1protected void Application_Start()
2{
3    // Enables use of spatial data types
4    SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin"));
5
6    // Other registrations...
7}

Asp.Net Websites

Open Default.aspx.cs and add

 1public partial class _Default : System.Web.UI.Page
 2{
 3    static bool _isSqlTypesLoaded = false;
 4
 5    public _Default()
 6    {
 7        if (!_isSqlTypesLoaded)
 8        {
 9            SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~"));
10            _isSqlTypesLoaded = true;
11        }
12        
13    }
14}

Desktop Applications

Add the following before any spatial operations are performed.

1SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);

3 – Tell Entity Framework which version of the assembly to use

Most of the explanations of how to solve the “‘Microsoft.SqlServer.Types’ version 10 or higher” error stopped at step 2.

After performing the steps that readme.htm outlined, I re-deployed the app, and things were still broken. Same exception. What gives??

This is where that second-most-popular answer came into play. It gave me the clue I needed.

Back in Global.asax.cs (or wherever you performed the steps for #2 above), add the following”

    SqlProviderServices.SqlServerTypesAssemblyName =
    "Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

Note: Depending on which version of the NuGet package you installed, you need to adjust the Version number from 14.0.0.0 to the correct version, as appropriate. I installed the latest, which at the time is version 14.x, so that’s what I put in.

??? The SqlServerTypesAssemblyName property doesn’t exist! ???

In my case, I ran into another small hurdle. When I tried to set the SqlServerTypesAssemblyName property, it didn’t exist!

Upon further inspection, I discovered that I only had Entity Framework version 6.1.**** installed. I updated to 6.1.3, and the property lit up. I set it appropriately, redeployed the app, and it magically worked.

Here’s hoping that this helps bring together several pieces of information to get you going with using SQL Server Geospatial data types with Entity Framework!

SQL Sever Synonyms, Entity Framework, and Integrated Security

Fri, 01 May 2015 17:44:31 +0000

Scenario

This may be an edge case, but I spent enough time (a couple of days) spinning on this that I thought it warranted a signpost on the Internet.

Suppose you have the following scenario before you:

Basics

You’re building an ASP.Net web application
Entity Framework has been chosen as your ORM
SQL Server (any version supporting Synonyms) is your backend database server of choice

Details

You’re using “Integrated Security=True” in your connection string to the database (in web.config)
The Application Pool in which your app resides is running under a domain service account (ie, not the default identity)
Your application primarily talks to one database, but there is information in another database that you need to pull into your app
To access the data in that other database, you’ve chosen to create a Synonym

Every bullet point in this scenario is normal and valid, until you get to the part about using “Integrated Security=True” in your connection string…

If you had chosen to use SQL Server authentication in your conenction string, and passed in a username and password for a SQL Server login that had permissions to the database, things would have worked out perfectly. I know this because that’s what I had been doing in my own application. I started running into the “Login failed…” error message once I switched to Integrated Security.

For some reason “Integrated Security=True” that throws Entity Framework for a loop. It accesses the database used in the DbContext instance just fine, but the second a line of code needs to use the object mapped to the Synonym, boom… crash… error…

Resolving the error

It turns out that the resolution is really, really simple. Instead of using a Synonym, use a View. You don’t have to change a single line of .Net code. Just make sure the view is named the same as the Synonym was (and drop the Synonym from the database, of course).

Creating the view

A Synonym essentially maps to an entire table, so the View’s structure could literally be a simple select * from [fully.qualified.database.objectName]. If your DBA hates select *, you’ll end up listing out every column that your Entity Framework object lists as one of its Properties.

Granting permissions

If the service account that’s running your Application Pool doesn’t already have permission to the database objects you created the View for, you need to go in and grant appropriate permissions to that account. Otherwise, you’re likely to get an error stating

The server principal “Domain\PrincipalName” is not able to access the database “DatabaseName” under the current security context.

Be specific and granular with the permission you grant to the account. If you’re just pulling in data for your app to display, only give the account SELECT permission on the database object your View references.

Wrapping up

In essence, using a Synonym that references a database object in another database with Entity Framework and Integrated Security just doesn’t work out in the end. Somehow, the security context loses the identity of the account running the application pool, and the dreated Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ rears its ugly head. Thankfully, the solution is simple: Create a View instead of a Synonym, grant approprate permissions, and you’ll be off and running (and you’ll avoid usernames and passwords in your web.config)!

SQL Server on Andrew Bancroft