SQL SERVER DBA, Linux and Azure

Sql Server : A connection was successfully established with the server, but then an error occurred during the login process. The certificate chain was issued by an authority that is not trusted

Error Message:

A connection was successfully established with the server, but then an error occurred during the 
login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority 
that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

The certificate chain was issued by an authority that is not trusted



Resolution:

Go to “Connect to Server” pop-up window >> Options >> Check “Trust server certificate“.


Once selected, SSMS will accept the remote certificate and complete connection.

Hope this helps,

Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library.

 Here are few more causes based on my search on the internet:

Network team doing Port Scanning

Sometimes Antivirus programs do check the port.

MSSQL server is under a DDoS

You may want to look at the error message and check the IP of the machine and see what that is.

Step 1:

First we need to figure out where is this database connection request coming from. Use DNS look up (nslookup a.b.c.d) command to identify the machine that is sending this connection.

Step 2:

Similarly, use Network command netstat -anp to uncover all the connections coming into the database server; Then filter them down to this particular IP (a.b.c.d) and see what port are they originating from.

Step 3:

Now go to that remote machine and run similar netstat -anp command on it. Now, compare and confirm that the PID for this process.

Step 4:

With the above two steps, you could find out exactly what software in that remote machine is trying to connect to the database server.

Now that you have the exact PID (ProcessID) on the remote machine, go to Task Manager and check under Processes tab to see the originating software name.

Step 5:

See if using Sql Authentication could help in allowing that remote machine to connect successfully (if its a valid login request).

There are some other unconventional approaches to digging in further, but the above steps should be sufficient in troubleshooting.

DDL CREATE_DATABASE EVENT alert via mail

 USE [master]

GO

/****** Object:  DdlTrigger [DDL_CREATE_DATABASE_EVENT]    Script Date: 12/4/2020 3:58:37 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [DDL_CREATE_DATABASE_EVENT]

ON ALL SERVER

FOR CREATE_DATABASE

AS

DECLARE @bd varchar(max)

Declare @tsql varchar(max)

Set @tsql = EVENTDATA().value

        ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)')

SET @bd = 'UserName: ' + UPPER(SUSER_NAME()) + '

         ServerName: ' + @@SERVERNAME + '

         Time: '   + CONVERT(varchar(25),Getdate()) + '

         HostName: ' + HOST_NAME() + '

         Database: ' + db_name() + '


         T-SQL: ' +  @tsql


BEGIN


PRINT 'Make sure you have informed all DBAs before creating databases. This event has been logged'


EXEC msdb.dbo.sp_send_dbmail


                      @profile_name = 'SQLMail',


                      @recipients = 'bajeyudu@sqldbanow.com',


                      @subject = 'A new database has been created!',


                      @body_format = 'HTML',


        @importance = 'High',


                      @body = @bd


END