10

One of our applications report on error message 2628:

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

instead of

String or binary data would be truncated in table 'mytable', column 'mycolumn'. Truncated value: 'myvalue'.

which I get with SSMS.

What setting do I need to change to get the full message?

I've tried to search for this, but all I get is that I need to set VERBOSE_TRUNCATION_WARNINGS to ON and compatibility_level to 150. Which I've done ages ago.

The application has this error handler:

 try { ES.isWorking = true; Worker worker = new Worker(new DBConnection(Settings.ConnectionString)); worker.DoWork(); ES.isWorking = false; } catch (Exception ex) { ES.isWorking = false; this.eventLog.WriteEntry("In OnTimer exception ! message: " + ex.Message.ToString()); errorHandler.HandleException(ex, "In OnTimer exception !", ErrorSeverities.Error); }

The errorHandler looks like this

Public Function HandleException(ByVal incommingEx As System.Exception, ByVal note As String, ByVal errorSeverity As ErrorSeverities, Optional ByVal sessionId As String = "", Optional ByVal applicationSource As String = "", Optional ByVal ignoreDB As Boolean = False, Optional ByVal noMail As Boolean = False) As Integer Dim stackTraceBuilder As New System.Text.StringBuilder Dim targetSite As String = "" Dim errorTime As DateTime = Now Dim messageBuilder As New System.Text.StringBuilder Dim innerEx As Exception = incommingEx.InnerException Dim errorId As Integer = 0 Dim innerCount As Integer = 1 Dim loggedToDb As Boolean = False Dim appSource As String = Me._applicationSource If applicationSource <> "" Then appSource = applicationSource End If '... more code' messageBuilder.AppendLine("(" + incommingEx.GetType.Name.ToUpper + ")" + vbCrLf + incommingEx.Message + " - SOURCE: " + incommingEx.Source) '...' While Not innerEx Is Nothing And innerCount <= 5 messageBuilder.AppendLine("INNER" + Convert.ToString(innerCount) + " (" + innerEx.GetType.Name.ToUpper + "):" + vbCrLf + **innerEx.Message** + " - SOURCE: " + innerEx.Source) If Not innerEx Is Nothing AndAlso Not innerEx.StackTrace Is Nothing Then stackTraceBuilder.AppendLine("INNER" + Convert.ToString(innerCount) + ": " + innerEx.StackTrace) 'Me.GetNumberedStack(innerEx)) End If innerEx = innerEx.InnerException innerCount += 1 End While '... more code where the error message is inserted in a log table, via a sproc' End Function

We have not seen this behavior before, but we have recently updated the .net framework to version 4.7.2, so it could be related.

the error text "string or binary" is comming from the bit that goes

  • innerEx.Message + and that is a simple built-in Exception data type. So it is VB and/or .net and/or SQL Server that is building the string, not us.

The error comes after a call to a stored procedure, where the user has probably entered a varchar(1000) text. The stored procedure adds a bit more, and the table hasn't got room for all 1100 bytes. Hence the error. The stored procedure does not have a try-catch block.

Improve this question
2
  • it happend again today, but on a different error number, and this time in SSMS, when logged in as sysadmin. Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the ****** value '******' to data type ******. I accidentially compared an int to a varchar(100). Could it be Azure SQL Database version related? Commented Aug 7, 2020 at 15:01
  • this was a bug in Azure SQL Database, and Microsoft will try to fix it in one of the next few releases. Commented Sep 8, 2020 at 7:36

5 Answers 5

Reset to default
13

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):

Screenshot of dynamic data masking rule set up in the Azure Portal

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.

Improve this answer
1
  • 1
    There are no masked columns in the database at all. But your answer may still be valid, because the user only have rights to execute stored procedures (no table access at all). Commented Aug 6, 2020 at 13:43
7

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.

Improve this answer
0
7

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

Improve this answer
6

The masking in the error message can occur also in scenarios unrelated to Dynamic Data Masking (or temporal tables). See the following example:

CREATE DATABASE ErrorMessageTest GO USE ErrorMessageTest GO CREATE USER SimpleUser WITHOUT LOGIN GO ALTER ROLE db_datareader ADD MEMBER SimpleUser GO CREATE FUNCTION dbo.Numbers(@MaxNumber int) RETURNS TABLE AS RETURN WITH Nbrs_4( n ) AS ( SELECT 1 UNION SELECT 0 ), Nbrs_3( n ) AS ( SELECT 1 FROM Nbrs_4 n1 CROSS JOIN Nbrs_4 n2 ), Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ) SELECT n FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n) FROM Nbrs ) D ( n ) WHERE n <= @MaxNumber ; GO CREATE FUNCTION dbo.Split(@Param nvarchar(MAX),@Separator NCHAR(1)) RETURNS TABLE AS RETURN SELECT SUBSTRING(@Param, N, CHARINDEX(@Separator, @Param + @Separator, n) - n) AS VALUE, ROW_NUMBER() OVER (ORDER BY N) AS Position FROM dbo.Numbers(LEN(@Param)) n WHERE SUBSTRING(@Separator + @Param, n, 1) = @Separator GO EXECUTE AS USER='SimpleUser' DECLARE @ApplyToMonth NVARCHAR(101) = '1,2,3,4,5,6, test error message,8' SELECT t.VALUE AS OriginalValue, TRY_CAST(REPLACE(t.VALUE, ' ', '') AS INT) AS IntValue FROM dbo.Split(@ApplyToMonth, ',') t SELECT x.OriginalValue, x.IntValue, CASE WHEN x.IntValue IS NULL THEN CAST(x.OriginalValue AS INT) ELSE 0 END AS RaisErrorColumn FROM ( SELECT t.VALUE AS OriginalValue, TRY_CAST(REPLACE(t.VALUE, ' ', '') AS INT) AS IntValue FROM dbo.Split(@ApplyToMonth, ',') t ) x GO REVERT GO USE master GO DROP DATABASE ErrorMessageTest

In SQL Server 2016 and SQL Server 2017, this produces the following error message:

Conversion failed when converting the ****** value '******' to data type ******.

Surprisingly, the solution in this case is the same as in the other cases: GRANT UNMASK TO SimpleUser

I am inclined to say that this is a bug, because in SQL Server 2019 CU5, I get the full error message (without granting UNMASK):

Conversion failed when converting the nvarchar value ' test error message' to data type int.
Improve this answer
5

The behavior you are seeing (i.e. meta-data being replaced by *** in error messages for non-sysadmin / non-dbo accounts) appears to be the effect of trace flag 3625: Metadata Visibility Configuration. I don't have an Azure account to test with, but the documentation does indicate that this feature is available in Azure SQL Database.

A far less likely possibility (in fact, it shouldn't even be possible at this point) is that someone configured the short-lived (thankfully!) "feature restrictions" option for ERRORMESSAGE, which was effectively the same thing as trace flag 3625. There were a few months, I believe, when the "Feature Restrictions" feature was being phased-out that it was possible for it to have been configured previously but no longer fully accessible. However, that feature should have been 100% removed (from Azure SQL Database) by March 4th, 2020 at the latest.

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.