The error sql72014 can occur in different situations that include when creating a new database, deploying, exporting, or importing the database.

Here at Bobcares, we have seen several causes for this error while troubleshooting SQL issues as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at the cause for this error and how to fix it.

What causes Error sql72014 to occur

There is no particular reason for this error to occur. This can occur in various situations like while creating a new database, while deploying the database or while importing and exporting and so on.

We can find the solution to this error by referring to the message that is present in the error itself.

For instance, the error appears as below.

How we fix Error sql72014

Now, let’s discuss the different cases where our customers came up with this error. Also, we will go through the solutions provided by our Engineers

1. One of our customers exported an Azure SQL Database to an Azure Storage Account. However, he received the below error message while importing the database to default SQL Server

Error SQL72014: .Net SqlClient Data Provider: Msg 12824, level 16, State 1, Line 5 The sp_configure value ‘contained database authentication’ must be set to 1 in order to alter a contained database. You may need to use RECONFIGURE to set the value_in_use.

We suggested enabling “contained database authentication” property. Because in order to restore a contained database that is coming from Azure SQL Database into an on-premise SQL Server instance the “contained database authentication” property must be enabled. For that, we asked to try below script:

sp_configure ‘contained database authentication’, 1;
GO
RECONFIGURE;
GO

Finally, this fixed the error.

2. In another case, a customer created a “.bacpac” from Azure SQL Database and then imported it into Local SQL Server. However, while creating the new database he got below error.

Error SQL72014: .Net SqlClient Data Provider: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ‘COLUMN’.
Error SQL72045: Script execution error. The executed script:
GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO PUBLIC;

Here is the solution provided by our Engineers.

• In Properties of Azure SQL database, we selected a Database Role named Public under the permission tab.
• Once it is selected, we can see VIEW ANY COLUMN ENCRYPTION KEY DEFINITION is Granted.
• We unchecked “VIEW ANY COLUMN ENCRYPTION KEY DEFINITION” and “VIEW ANY COLUMN MASTER KEY DEFINITION”.

This fixed the error.

3. In another case,  the database upgrade failed and the customer received the below error.

Error SQL72014: .Net SqlClient Data Provider: Msg 14525, Level 16, State 1, Procedure sp_delete_job, Line 103 Only members of sysadmin role are allowed to update or delete jobs owned by a different login.

Here, the installer was using an account to connect to the Database that didn’t have enough permissions.

So, we added sysadmin role to that account and re-run the installer to fix the error.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, This error can arise in different situations like creating a new database, deploying, exporting, or importing the database. Today, we saw the resolution to this SQL error.