I am importing CSV through .Net code and getting the data in data table and trying to bulk insert using SqlBulkCopy but i am getting certificate error. when i try to do the other operation there is no certificate error its only when i perform SqlBulkCopy. Here is error description
Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are: '7F-1D-20-E1-43-0B-B5-92-66-78'. Certificate with thumbprint 'XXXXXXXXXXXXXXXXXXXXXXXXXX' not found in certificate store 'My' in certificate location 'CurrentUser'. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store. Parameter name: masterKeyPath
i did the certificate import in local machine. below is code i have data in dt datatable object. that is returned from worksheet.Cells.ExportDataTableAsString.
Dim copy As New SqlBulkCopy(ConnString, SqlBulkCopyOptions.KeepIdentity Or SqlBulkCopyOptions.AllowEncryptedValueModifications)
copy.DestinationTableName = "Customer"
copy.ColumnMappings.Add("CustID", "CustID")
copy.ColumnMappings.Add("SSN", "SSN")
copy.WriteToServer(dt)
taking the reference - https://dba.stackexchange.com/questions/160577/is-it-possible-to-bulk-insert-data-into-a-table-that-has-columns-encrypted-with
if we go as per above we need to do the 2 round to insert the data.i have datatable object returned by Worksheet ExportDataTableAsString. when i directly use the datatable for SQLbulkcopy i get the the certificate missing error.
can someone help me on this and suggest better way to do it.
