SQL Server Connectivity issue

I recently received an escalation of an issue where an application server was not able to connect to few of the database servers however one production database server was accessible. While connecting from SQL Management Studio following exception was raised.


A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)


This exception shows that the connectivity between application and database instance is fine, however, during login handshake there is some issue.


I enabled below server traces to identify more details of socket errors.


DBCC TRACEON (3689, 4029, -1)


After enabling these traces, I notice below error that gave more information to understand the issue.


Network error code 0x80090331 occurred while establishing a connection; the connection has been closed. This may have been caused by client or server login timeout expiration. Time spent during login: total 2 ms, enqueued 0 ms, network writes 0 ms, network reads 2 ms, establishing SSL 2 ms, negotiating SSPI 0 ms, validating login 0 ms, including user-defined login processing 0 ms.


This error indicate that both servers are using different ciphers to encrypt while making connection.
I found that Triple DES 168 was mismatching with Application server so just removed and it resolved the problem.




Reference:


https://blogs.msdn.microsoft.com/winsdk/2014/05/01/encountering-the-client-and-server-cannot-communicate-because-they-do-not-possess-a-common-algorithm-or-sec_e_algorithm_mismatch-0x80090331/


https://social.technet.microsoft.com/Forums/office/en-US/bcc6be0b-0385-4aff-82ae-b6e973fe398d/0x80090331-the-client-and-server-cannot-communicate-because-they-do-not-possess-a-common-algorithm?forum=winserver8gen

Deadlock information from "system health" extended events

In SQL 2012 and above versions we can reterive deadlock information without enabling trace flags. Steps as below.

1) Go to Management -> Extended Events -> Sessions -> system_health

2) Right click on “package0.event_file”

3) Select “View Target Data..”

4) Click on “Filters” from task bar as below

5) Apply filter on Name field with “xml_deadlock_report”

6) Select on any deadlock based on timestamp, select deadlock tab for more info.

Fixing an SSRS Password Error while Changing Credentials

While applying the domain accounts suddenly an error arose.

“The password is not valid. Please retype the password”,

This appeared whenever a domain account name was entered in user name field. I tried the other options to reset the error and allow me to test the connection but was repeatedly prompted for credentials with the same error even if the correct credentials are entered.

Refer my post below for more details

http://www.sqlservercentral.com/articles/SSRS/155071/

SQL Server Crashed EXCEPTION_ACCESS_VIOLATION

I recently got escalation to investigate a SQL instance that was unexpectedly shutdown due to access violation, no other error or message was logged in Application/System/SQL error log except a memory dump.

Exception Address = 00000000776CE660 Module(ntdll+000000000004E660)

Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION

Access Violation occurred reading address 0000000000000000

So I decided to review the dump file using windbg and public symbols. Stack shown me the very last function was OraOLEDBButl11 which is Oracle driver for linked server so it was clear that distributed query is culprit here and right after OS started to release the heap memory and exception dispatched which is very strange behaviour, while searching for OraOLEDBButl11 I found a reference that gave me more info about Oracle driver related bug, for more info refer below reference.

Reference:

https://support.microsoft.com/en-us/help/2295405/sql-server-service-crashes-when-you-run-an-oracle-linked-server-query

Justify the Remote access requirement for Biztalk Log Shipping

I have been in a situation where our Security compliance team requested to prove that remote access configuration is indeed mandatory to setup log shipping for a Biztalk database. As all we know this configuration option is used to execute remote stored procedures from local server or allow access from remote server to execute a local stored procedure. Of course this has to be accompanied with a linked server that provide RPC_OUT services.

Refer below my article for more details.

http://www.sqlservercentral.com/links/337157/360187

Top Processes consuming Memory and CPU

It is at time important to find out what other processes are doing in term of CPU and memory utilization, following commands are helpful for ad-hoc or reporting purpose.

Top 5 Memory consuming processes:

Powershell > get-wmiobject WIN32_PROCESS | Sort-Object -Property ws -Descending|select -first 5|Select processname, @{Name="Mem Usage(MB)";Expression={[math]::round($_.ws / 1mb)}},@{Name="ProcessID";Expression={[String]$_.ProcessID}},@{Name="UserID";Expression={$_.getowner().user}}

Memory consuming processes more than 100MB

tasklist /FI "MEMUSAGE ge 100000"



CPU consuming processes:

wmic path Win32_PerfFormattedData_PerfProc_Process where PercentProcessorTime!=0 get Name,PercentProcessorTime







Top CPU consuming processes that are running for more than 5 minutes:

tasklist /fi "cputime gt 00:05:00"

DBCC CHECKDB failed due to invalid object id

This anomaly persist due to deleting objects that are not removed from system tables properly due to some reasons which is in fact ignored in legacy SQL version (2000) however catch up by DBCC in later versions (Most probably after migration or upgrade).

Error Description:

Error 3854: [Microsoft][ODBC SQL Server Driver][SQL Server]Attribute (parent_object_id=1977058079) of row (object_id=1993058136) in sys.objects has a matching row (object_id=1977058079) in sys.objects (type=S ) that is invalid

Workarounds:

1) Ignore the errors, though database consistency check will not able to complete.

2) Export the data to new database using import/export utility.

3) Remove the anomaly from system base table, as system table (sysschobjs) is not directly accessible so DAC access is required, steps as below.

- Enable configuration (allow update and DAC)
- Restart SQL in single user mode with –m
- As both objects (1977058079, 1993058136) are in same table along with syskeys table so delete the child record 1993058136.
- delete  FROM sys.sysschobjs WHERE ID=1993058136;
- remove –m parameter and restart SQL in normal model
- disable allow update and DAC