Here at IbmiMedia, We have seen numerous complaints and support requests from our clients regarding Sql Database related issues and bugs as part of our Server Support Services.

In this context, we shall look into the main causes of this bug and how best to fix it.

Why does Error sql72014 occur?

There are many reasons why the error triggers. It happens when a new database is being created, exporting and importing process and other SQL query actions.

The best method to know why this error occur is to check the contents of the error message carefully to get an idea about it.

How to solve sql72014 Error

Now let us see the cases of different clients that had this error. We shall also show you how our Server Experts fixed it quickly.

i. One of our client tried to import a database from his Azure SQL Database to another Azure Storage account. Along the way, he got an error message prompt while trying to make an import query on the SQL server. It looked like this;

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.

From the above message , it relates to the "contained database authentication" value. Therefore, we enabled the "contained database authentication" property. Using the "contained database authentication" property is very important in order to restore a contained database which you are trying to export from an Azure SQL Database. To enable this property, use the query below;

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

This helped solve the error.

ii. In  another scenario, a client created a ".bacpac" file from his Azure SQL Database and tried to import it to a Local SQL Server. During this process, he got an error message as stated below;

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;

To fix this issue, you can do the following;

* Select a Database Role with name Public which you can see in the permission tab of the Properties section of Azure SQL database.

* As soon as you select it, look at for the status of the VIEW ANY COLUMN ENCRYPTION KEY DEFINITION is Granted.

* Then, you have to uncheck the "VIEW ANY COLUMN ENCRYPTION KEY DEFINITION" option as well as the "VIEW ANY COLUMN MASTER KEY DEFINITION" one.

You will see that the issue is fixed.

iii. In a different case, while a client was trying to do an upgrade process of the database, it failed! The error looks like this;

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.

In this case , there was not enough permission by the user account trying to connect with the Database.

A quick fix was to add sysadmin role to the affected account and now the upgrade process will be successful.

Do you need support in solving SQL errors? Get through to us.