Incorrect syntax near 'TRIPLE_DES'. - SQL Server throws an error when try to use algorithms

Assume that you use SQL Server 2016 and trying to create a Symmetric Key or Asymmetric Key for encrypting data. If you try use an algorithm like TRIPLE_DES, you will get the mentioned error;

Msg 102, Level 15, State 1, Line 20

Incorrect syntax near 'TRIPLE_DES'.

Here is a sample code for seeing this error;

-- Create a database
CREATE DATABASE Sales;
GO

-- Connect with newly cerated database
USE Sales;
GO

-- Create a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO 

-- Create a certificate for protecting the our key 
CREATE CERTIFICATE SalesCertificate WITH SUBJECT = 'Sales Certificate';
GO

-- Create the key for encrypting data
-- Note that the created certificate protects it.
-- However, this will throw an error
CREATE SYMMETRIC KEY SalesKey
WITH ALGORITHM = TRIPLE_DES  
ENCRYPTION BY CERTIFICATE SalesCertificate;
GO

Now, what is the reason for this. The reason for this is, this algorithm is deprecated in SQL Server 2016. Not only that, All Algorithms except AES_128, AES_192, and AES_256 are deprecated in SQL Server 2016. 

What if you still need one of these deprecated algorithms? Yes, it is possible, but you need to downgrade the database by changing the Compatibility Level 120 or below.

This code shows the way of doing it.

USE master;
GO

-- Change the compatibility level to 120
ALTER DATABASE Sales
SET COMPATIBILITY_LEVEL = 120;

-- And check again
USE Sales;
GO

-- This will work now without any error
CREATE SYMMETRIC KEY SalesKey
WITH ALGORITHM = TRIPLE_DES  
ENCRYPTION BY CERTIFICATE SalesCertificate;
GO

Even though you can use the algorithm after changing the Compatibility Level, remember lower Compatibility Level might not let you to use all functionalities available with SQL Server 2016. Therefore, if possible, use allowed algorithms only.

Ignore or disable string truncation error and insert or update records

String or binary data would be truncated error message is a well known error message and you get this when you try to set a value with higher characters than the column accepts. For an example, if you have configured a column with varchar(5) and you try to set a value with 6 characters, you will get the truncation error and statement will be failed.

Can we ask SQL Server to ignore this and continue with truncation and set the value with possible characters? Yes, it is possible with ANSI_WARNINGS setting. The default value of this is ON, hence INSERT and UPDATE statements work as specified by the ISO standard. That is the reason for the error we experience.

If you set this setting for OFF, then no warning is issued and statement succeeds. However value is truncated as per the column size set.

Here is an example;

USE tempdb;
GO

DROP TABLE dbo.TestTable
GO

CREATE TABLE dbo.TestTable
(
 Id int PRIMARY KEY
 , Value varchar(5) NOT NULL
);
GO

-- This statement fails as we send 11 characters
INSERT INTO dbo.TestTable
 (Id, Value) VALUES (1, 'Test value.');

-- This code succeeds but will take only first 5 characters
SET ANSI_WARNINGS OFF
INSERT INTO dbo.TestTable
 (Id, Value) VALUES (2, 'Test value.');
SET ANSI_WARNINGS ON

A nonrecoverable I/O error occurred on file Backup to URL received an exception

My previous post discussed SQL Server Backup to URL, this is an error related to it.

​A nonrecoverable I/O error occurred on file "https://dinesqlclassicstorage.blob.core.windows.net/classisstroagecontainer/Sales_20160512.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (404) Not Found..

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

As usual, did a search but did not find a good solution but figured out a possible reason for this. The reason could be, using a Azure Classic Storage. If we try to use the newest storage, this error does not occur. However, I cannot exactly say that this is the only reason for this error. There can be many other reasons but try with new storage and see if you experience the same.

Could not load schema model from package. (Microsoft.SqlServer.Dac)

Once I wrote a post on how to take a copy of Azure SQL Database into on-premise server: Taking backups of Azure SQL Database and restoring to on-premise server. It was a simple method, all we have to do is, export the database into a bacpac file and add to the server using Import Data-tier Application... 

With new operating system installation, I was trying to do the same but was hit by an error;

Could not load schema model from package. (Microsoft.SqlServer.Dac)

Internal Error. The database platform service with type

Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider is not valid.....

I immediately realized the issue, it was all about the version of Management Studio. I tried the same with Management Studio April Preview and I was able to add the database exported from Azure.

As per the reading I did, if you try with Management Studio that comes with SQL Server 2014, you need Service Pack 1 and CU 5 or above.

Methods to Fix SQL Server Error 4064 Login Failed For User [Guest Post]

This post is written by Andrew Jackson

Overview

SQL Server Database helps users to create their own databases where they can store or retrieve data whenever required. Sometimes, user may encounter error while connecting to the database and it displays an error “Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)” This SQL Server Error message appears when SQL Server Login is not able to connect to the default database. The post will be discussing about the SQL Server Error 4064 and the way to resolve it.

SQL Server Error 4064 Login Failed For User

Each user that has an account on the SQL Server belongs to a database by default. When user tries to connect to a PC running SQL Server without specifying login database, the default database is used. However, if the default database is unavailable at the time of connection, the SQL Server Error 4064 will be displayed. 

Possible Causes of the SQL Server Error 4064

Some of the causes for the unavailability of the database are as follows:

• DB is in single user mode & only available connection is used already.
• When your DB no longer exists 
• Database is in suspect mode 
• DB has been detached 
• Database is offline
• DB is set to emergency status
• Does not have login account mapped to the user
• User trying to login has denied access.
• DB is part of a Database Mirror

How to Fix the Error 4064

One solution to avoid the error when the user’s default database is unavailable, is to logon as a user that can modify logins and change the user’s default database to a database that is currently available for a connection.

Steps for fixing the error 4064 are as follows:

- Before login to the user’s database, go to Options that is in right corner of the login window.

- Go to Connection Properties Tab of the login dialog box, enter the ‘master’ database in dropdown box changing default database to master, and click on Connect.

After successful login in the system, type the following TSQL command for your username and database.

User must make sure that they changes [test] with their own username and master with their database name. 

Alternative Method for Resolving Error Message 4064

• In SQL Server 2000 & SQL Server 7
  
  OSQL Utility can be used to change the default’s database by following the steps:
1. In the command prompt window, type the following ‘C :\> osql -E -d master’ and press Enter.
2. Type ‘1>sp_defaultdb 'user's_login', 'master'’ at the osql prompt and press Enter
3. Type ‘2>go’ at the second prompt and press Enter
• In SQL Server 2005 & later versions
  
  SQLCMD utility is used to change the default database server by following the steps below:
1. Go to Start -> Run. Type cmd and press ENTER.
2. SQL Server Login can use either Windows authentication typing the following in command prompt ‘sqlcmd –E -S InstanceName –d master’ or SQL Server Authentication with ‘sqlcmd -S InstanceName -d master -U SQLLogin -P Password’
   {InstanceName =Name of SQL Server Instance to which user wish to connect
   SQL Login=Server Login whose database created by default has got dropped
   Password=SQL Server Login Password}
3. Type ‘ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName’ at the sqlcmd prompt and press ENTER
4. Now, Type ‘GO’ and press ENTER.

Conclusion

The post is aimed to guide users in resolving one of the most common errors faced by users while trying to connect to the database that is unavailable at the time of connection. The possible causes of the SQL Server Error 4064 login failed for user are discussed as well. It further defines solutions to change the unavailable database to any valid database on the server in order to resolve the 4064 Error.

How to get system/error messages with severity level lower than 19 logged to Application Log

Have you noticed that SQL Server does not send all messages to Application Log? The default setting does not send all messages, generally messages with severity level between 19 and 25 are automatically written but not all. The best way to check which messages that are getting logged, is checking sys.messages catalog view. This has all messages and the column is_event_logged indicates whether the messages will be logged or not.

Now, what if you need to get a message logged which is not get logged automatically? Good example is, a message related to deadlock. If you check this message, you will see that its is_event_logged is not set to 1.

SELECT * FROM sys.messages 
WHERE language_id = 1033 AND severity = 13;

If required, this can be enabled for logging. It can be done using sp_altermessage stored procedure. Here is the way of doing it.

EXECUTE sp_altermessage 1205, 'WITH_LOG', 'True';

Now it is enabled. If you run the first code again, result will be just like this:

Let's test this. Below code generates a deadlock.

USE master;
GO

-- Create a sample database
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'DeadlockTestDB')
 DROP DATABASE DeadlockTestDB;
GO

CREATE DATABASE DeadlockTestDB;
GO


-- Add two tables and insert two records
USE DeadlockTestDB;
GO

CREATE TABLE Table01 (Id int PRIMARY KEY, Name varchar(200));
GO
INSERT INTO Table01 VALUES (1, 'Table01 value');
GO

CREATE TABLE Table02 (Id int PRIMARY KEY, Name varchar(200));
GO
INSERT INTO Table02 VALUES (100, 'Table02 value');
GO


-- Create two stored procedures.
-- This one updates a record in Table01, waits, 
-- and updates Table02.
CREATE PROC UpdateTable01andTable02
AS
BEGIN

 BEGIN TRAN

  UPDATE Table01
   SET Name = 'Updated'
  WHERE Id = 1;

  WAITFOR DELAY '00:00:10';

  UPDATE Table02
   SET Name = 'Updated'
  WHERE Id = 100;

 COMMIT TRAN
END;
GO

-- This one updates a record in Table02 and Table01
CREATE PROC UpdateTable02andTable01
AS
BEGIN

 BEGIN TRAN

  UPDATE Table02
   SET Name = 'Updated - 2'
  WHERE Id = 100;


  UPDATE Table01
   SET Name = 'Updated -2'
  WHERE Id = 1;

 COMMIT TRAN
END;
GO

/*

-- Open a new connection and run this code
USE DeadlockTestDB;
GO

EXEC UpdateTable01andTable02;
GO

-- Open another connection and run this.
-- Make sure this run just after you start the above execution
USE DeadlockTestDB;
GO

EXEC UpdateTable02andTable01;
GO
*/

Once you get the code executed and received the deadlock error, open the Event Viewer and see. You should see something like this;