string or binary data would be truncated in table '******', column '******'. Truncated value: '******'

It looks like this table was defined with dynamic data masking, and the user that the application uses to access the database doesn't have permission to view masked data (which is good!).

This is why the behavior differs between the application and SSMS: I expect you're using a higher-privileged user when running the query from SSMS

Here's a demo:

CREATE TABLE dbo.Test
(
    Id int IDENTITY PRIMARY KEY,
    Filler varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL
);
GO

CREATE USER TestUser WITHOUT LOGIN;  
GRANT SELECT ON dbo.Test TO TestUser;  
GRANT INSERT ON dbo.Test TO TestUser;
GO

EXECUTE AS USER = 'TestUser';
INSERT dbo.Test 
    (Filler)
VALUES
    (REPLICATE(N'A', 101)); 
REVERT;

The result of which is:

Msg 2628, Level 16, State 1, Line 12
String or binary data would be truncated in table '******', column '******'. Truncated value: '******'.

To get the full message in the application, you'll need to grant the user the UNMASK permission:

GRANT UNMASK TO TestUser;

Running the INSERT code again results in the full error text:

Msg 2628, Level 16, State 1, Line 14
String or binary data would be truncated in table 'tempdb.dbo.Test', column 'Filler'. Truncated value: 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'.

---

When using Azure SQL Database, Dynamic Data Masking can also be configured in the Azure Portal user interface. You may want to check there (although as far as I can tell, masks set up this way still update the sys.columns metadata):

I would also check if there are other databases that are part of the same logical SQL Server instance in Azure - maybe there are some kind of strange, cross-database queries going on.

I think it is related to a new and underreported bug in Azure SQL Database (only), where Temporal Tables are treated different from normal tables, when the errors 245 and 2628 occur.

I’ve found that I had added Sensitivity Classification to all my Temporal Tables, (and only to some of my non-Temporal Tables).

As in

add SENSITIVITY CLASSIFICATION to dbo.Mytable.name with (LABEL = 'General', INFORMATION_TYPE = 'Public')

when I drop the Sensitivity Classification, the old error message is back.

I have an example that demonstrates the error:

drop table if exists dbo.mytable
go
create table dbo.Mytable (i int not null identity(1,1) primary key clustered, name varchar(10) not null)
go
insert into dbo.Mytable ( name ) values ( 'abc' )
go
select * from dbo.Mytable where name = 1  -- programming error; data type mis-match

returns

(1 row affected)
Msg 245, Level 16, State 1, Line 8
Conversion failed when converting the varchar value 'abc' to data type int.

This is the correct error message. Now run this:

add SENSITIVITY CLASSIFICATION to dbo.Mytable.name with (LABEL = 'General', INFORMATION_TYPE = 'Public')

and when you run this

select * from dbo.Mytable where name = 1 -- programming error; data type mis-match

You will see this

Msg 245, Level 16, State 1, Line 169
Conversion failed when converting the ****** value '******' to data type ******.

This is the in-correct error message.

I've tried this code on Azure SQL Database where I get the in-correct message, and on SQL Server 2019 CU6, where I only get the correct message.

I have reported this to Microsoft, and they have acknowledged that this is a bug, and that it will be fixed in a few months.

I have created two scripts to 1) document existing classifications, and 2) drop all those classifications.

The scripts can be found here: document and drop classifications