CODEWITHSUNDEEP
c#sql-serversql-server-2016always-encrypted
F.S.
F.S.

Reputation: 353

User Defined Table Type Parameter with Always Encrypted

I am passing in a SQL parameter which is of a user defined table type (idlist) to a stored procedure - all was working well till I added Column Encryption Setting=Enabled; to the web.config to make the app work with SQL's Always Encrypted feature.

var paramFilterFacilityList = new SqlParameter();
paramFilterFacilityList.ParameterName = "@f";
paramFilterFacilityList.TypeName = "dbo.idlist";
paramFilterFacilityList.Value = fValue;
paramFilterFacilityList.SqlDbType = SqlDbType.Structured;

Exception raised is :

The table type parameter "@f" must have a valid type name.

Any ideas on what could be going on here?

Upvotes: 1

Views: 1387

Answers (2)

Arvind Shyamsundar
Arvind Shyamsundar

Reputation: 11

At the moment we do not have inbuilt support for TVPs and Always Encrypted in any of the supported drivers (.NET / ODBC / JDBC.) Please review my blog post at https://blogs.msdn.microsoft.com/sqlcat/2016/08/09/using-table-valued-parameters-with-always-encrypted-in-sql-server-2016-and-azure-sql-database/ which proposes a workaround.

Upvotes: 0

Victoria Malaya
Victoria Malaya

Reputation: 516

Always Encrypted doesn't support TVPs. When you use enable "Column Encryptin Setting" you can't use TVPs at all.

Upvotes: 3

Related Questions