[SOLVED] CRUD operation not work with ADODB and SQL server using MSOLEDBSQL19 Provider and "strict" option (TLS1.3)

CRUD operation not work with ADODB and SQL server using MSOLEDBSQL19 Provider and "strict" option (TLS1.3)

In classic ASP pages, I am trying to connect SQL server 2k22 using the ADODB connection and MSOLEDBSQL19 driver(provider). On the SQL Server Side, I enabled the Force encryption=Yes and client-side(ASP pages) in connection string added Use Encryption for data=optional Key. Also, proper certificates are applied on the SQL server as well as on the client side. In this situation Connection is established between ADDOB and SQL server using TLS 1.2 (TDS 7) protocol. and all types of query (CRUD) working properly.

<%
dim sDept
sDept="Batting"
set conn=Server.CreateObject("ADODB.Connection")

conn.mode=3
conn.Open "Provider=MSOLEDBSQL19;Use Encryption for data=Optional;Password=Password@123;Persist Security Info=True;User ID=sa;Initial Catalog=Testdb;Data Source=sql2k22.test.com;Application Name=Test App"
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from Players where dept = '" & sDept & "'" , conn,2,3,1
rs("Name")="Rohit"
rs.update

do until rs.EOF
    for each x in rs.Fields
       Response.Write(x.name)
       Response.Write(" = ")
       Response.Write(x.value & "<br>")
    next
    Response.Write("<br>")
    rs.MoveNext
loop

rs.close
conn.close
%>

Now I want to Use TLS 1.3 Protocol for that purpose change the key value, Use Encryption for data=strict. after this change SQL connection was established using TLS 1.3 protocol and was able to fetch (SELECT query) records from SQL table.

conn.Open "Provider=MSOLEDBSQL19;Use Encryption for data=Strict;Password=Password@123;Persist Security Info=True;User ID=sa;Initial Catalog=Testdb;Data Source=sql2k22.test.com;Application Name=Test App"
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from Players where dept = '" & sDept & "'" , conn

but when I Used the "Update query" it failed. and shows error

ADODB.Recordset error '800a0cb3' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

and if we provided the lock type during recordset.open rs.Open "Select * from Players where dept = '" & sDept & "'" , conn,2,3,1 then it failed with error

Microsoft OLE DB Driver 19 for SQL Server error '80004005' The incoming tabular data stream (TDS) protocol stream is incorrect. The stream ended unexpectedly.

Environment:

1:SQL Server 2K22 (tried with SQL EXPRESS as well as SQL Developer). 2:MSOLEDBSQL driver:version 19.3.2. 3:client and server side: win2k22 server OS 4:classic ASP and ADODB connection.

I also tried with Encrypt=strict key in the connection string but the connection happened over TLS 1.2 protocol (TDS 7 pre-login).

I want to do CRUD operation over TLS 1.3 protocol using ASP-ADODB-MSOLEDBSQL19-SQL(2k22) on Win 2k22 server OS.

Solution

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor)

Your SQL Server 2022 server is unpatched. I suggest you install the latest CU (currently CU 10), especially considering TDS 8 was introduced with SQL Server 2022 and the CUs include fixes to TDS 8 and related encryption.

Be aware that TDS 8 and TLS 1.3 are relatively new technologies so interop with legacy ADO (which hasn't been enhanced for 20+ years) may present additional challenges.