Learn how to conceal a Database Engine instance in SQL Server using SQL Server Configuration Manager. SQL Server relies on the SQL Server Browser service to list installed Database Engine instances on the computer. This facilitates client applications in searching for a server and aids clients in distinguishing between multiple Database Engine instances on a single computer. Follow the steps below to stop the SQL Server Browser service from revealing a Database Engine instance to client computers attempting to locate it through the Browse button.
Using SQL Server Configuration Manager
To hide an instance of the SQL Server Database Engine
In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for<server instance>, and then select Properties.
On the Flags tab, in the HideInstance box, select Yes, and then click OK to close the dialog box. The change takes effect immediately for new connections.
Once you enable this, restart your MS SQL Server Services to make changes to take effect.
SQL Server Management Studio offers utilities for setting up, overseeing, and overseeing both SQL Server and information instances.
When automatic updates are enabled for SQL Server Management Studio, you will receive notifications whenever a new SSMS update becomes available.
This guide outlines the procedures for updating SQL Server Management Studio (SSMS). You can effortlessly advance to the latest version of SSMS by installing the most recent release from the Microsoft website.
How to Update SQL Server Management Studio (SSMS)?
There are two ways to Update SQL Server Management Studio (SSMS):
1) Install the SQL Server Management Studio updates.
2) Download the latest SQL Server Management Studio and install it manually.
Steps to update SQL Server Management Studio(SSMS):
1: Open SQL Server Management Studio.
2: Click on the “check for updates” under the tools section.
3: The SQL Server Management Studio Updates box opens. Here you can manage automatic updates to SSMS. To get the latest update of SQL Server Management Studio, click on the Update button.
4: Once, you click on Update Button. You will be redirected to the Download SQL Server Management Studio page.
5: Under the Download SSMS heading, click Free download for SQL Server Management Studio (SSMS). Download the SSMS-Setup-ENU.exe installer.
7: Now run the SSMS-Setup-ENU.exe as administrator.
8: On the Welcome page of SQL Server Management Studio, click Install.
9. Once, installation is completed. You may need to restart your system.
Once the System is up, you will be able to use the latest version of the SQL Server Management Studio
Data corruption in the context of SQL Server refers to the unintentional and undesirable changes or errors in the stored data. This can happen due to various reasons, such as hardware failures, software bugs, or human errors.
Hardware failures like disk crashes, power outages, or faulty memory can lead to data corruption. Software bugs in the SQL Server itself or in applications interacting with it may also cause corruption. Human errors, such as improperly designed queries or incorrect data updates, can contribute to data corruption as well.
When data corruption occurs, it can result in data inconsistency, loss of data integrity, and potential disruptions to the normal functioning of the SQL Server database. It’s crucial to regularly backup data and implement preventive measures like using RAID systems, uninterruptible power supplies (UPS), and monitoring tools to detect and address potential issues before they lead to corruption.
Impact of Data Corruption:
Data corruption can have significant impacts on a system and its data. Here are some of the potential consequences:
Data Loss: Corruption may lead to the permanent loss of data. In severe cases, corrupted data may be irrecoverable, especially if proper backups are not in place.
Data Inconsistency: Corruption can result in inconsistencies within the database. Inconsistent data may lead to incorrect results when querying the database, making it challenging to rely on the accuracy of the information.
System Downtime: Resolving data corruption issues often requires downtime for the affected system. During this time, the database may be inaccessible to users, causing disruptions to business operations.
Financial Loss: Data corruption can have financial implications, especially if critical business data is affected. It may lead to lost revenue, increased operational costs, and potential legal consequences.
Reputation Damage: If data corruption leads to data breaches or loss of customer information, it can damage the organization’s reputation. Customers may lose trust in the company’s ability to secure and manage their data.
Operational Challenges: Corrupted data can hinder normal business operations. For example, if an essential report cannot be generated accurately due to corrupted data, it may impact decision-making processes.
Resource Intensive Recovery: Recovering from data corruption can be resource-intensive in terms of time, effort, and costs. It may involve data restoration from backups, database repair operations, and thorough testing to ensure the system’s stability.
Legal and Compliance Risks: In some industries, data corruption and loss may lead to legal and compliance issues. Failure to protect sensitive data or comply with regulations can result in penalties and legal consequences.
How to Prevent Data Corruption?
Preventing data corruption in SQL Server involves a combination of best practices, regular maintenance, and the implementation of preventive measures. Here are some strategies:
Regular Backups:
Implement a robust backup strategy with regular backups of your database. This ensures that you have a recent and clean copy of your data that can be restored in case of corruption.
Transaction Logs:
Regularly back up transaction logs to allow for point-in-time recovery. This can help in recovering data to a specific point before the corruption occurred.
Database Maintenance:
Perform regular maintenance tasks, such as index rebuilds and database consistency checks using DBCC CHECKDB. This helps identify and fix potential issues before they lead to corruption.
Hardware Monitoring:
Monitor the health of your hardware, including disks and memory. Implement redundant and fault-tolerant hardware configurations to minimize the risk of failures.
RAID Configuration:
Use RAID (Redundant Array of Independent Disks) configurations to provide fault tolerance. RAID setups can help prevent data loss in case of a disk failure.
Uninterruptible Power Supply (UPS):
Install UPS systems to protect against power outages and fluctuations, which can lead to data corruption. This ensures that the server has sufficient power to shut down gracefully in case of an outage.
Regular Software Updates:
Keep the SQL Server software and operating system up to date with the latest patches and updates. Software updates often include bug fixes and improvements that can enhance stability and security.
Error Handling and Logging:
Implement thorough error handling in your applications and scripts. Log errors and exceptions to facilitate quick identification and resolution of issues that may lead to corruption.
Isolation Levels:
Use appropriate isolation levels in transactions to prevent data inconsistencies. Understanding and implementing the right isolation level for your application can reduce the risk of concurrency-related issues.
Monitoring and Alerts:
Set up monitoring tools to regularly check the health of your SQL Server. Implement alerts to notify administrators of potential issues or anomalies that could lead to corruption.
Regular Testing:
Test your backup and recovery procedures regularly to ensure that you can successfully restore data in case of a corruption event.
By combining these practices, you can create a comprehensive strategy to prevent data corruption and minimize its impact on your SQL Server environment.
How to check if data is corrupted?
Detecting data corruption in SQL Server involves using various tools and techniques to identify inconsistencies or errors in the database. Here are some methods:
DBCC CHECKTABLE and DBCC CHECKALLOC:
For more granular checks, you can use DBCC CHECKTABLE to check a specific table and DBCC CHECKALLOC to check allocation structures. These commands can help pinpoint the location of corruption.
DBCC CHECKTABLE('YourTableName') WITH NO_INFOMSGS, ALL_ERRORMSGS; DBCC CHECKALLOC('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB:
Use the DBCC CHECKDB command to check the logical and physical integrity of all objects in a database. This command performs various checks and can identify and repair corruption issues.
CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
SQL Server Management Studio (SSMS):
In SSMS, you can use the “Check Database Integrity” task. Right-click on the database, go to Tasks, and select “Check Database Integrity.” This will run DBCC CHECKDB against the selected database.
Monitoring Tools:
Implement monitoring tools that can regularly check the health of your SQL Server. These tools can generate alerts if they detect anomalies or potential corruption issues.
Error Logs:
Examine SQL Server error logs for any entries related to corruption. Events and error messages indicating issues with the database’s integrity may be logged.
Application Error Handling:
Design your applications with robust error handling mechanisms. Log errors and exceptions, and monitor application logs for any signs of data inconsistency.
Checksums and Hash Functions:
Use checksums or hash functions to validate the integrity of specific data. You can store checksums or hashes for critical data and regularly compare them to identify discrepancies.
Regular Audits:
Conduct regular audits of your data by comparing key values or aggregates to expected results. This can help identify subtle data inconsistencies that may be indicative of corruption.
Third-Party Tools:
Consider using third-party tools designed for database monitoring and corruption detection. These tools often provide additional features and can automate the detection process.
Isolation Levels and Blocking:
Monitor for issues related to concurrency, such as blocking or deadlocks, which can sometimes be indicative of corruption. Use appropriate isolation levels in your transactions.
Regularly running these checks and monitoring tools can help detect data corruption early, allowing you to take corrective action before it leads to more significant issues.
Here are some basic SQL Server steps which you can follow which would ensure SQL Security.
Authentication Method: Use Windows authentication (Active Directory based) instead of SQL Server authentication to enhance security.
Access Control: Employ server, database, and application roles to regulate data access, ensuring that users possess the necessary permissions.
File System Permissions: Safeguard the physical database files by applying NTFS permissions to restrict file system-level access.
Strong SA Password: Establish a robust and unpredictable SA (System Administrator) password to thwart unauthorized entry to the server.
Note: Disable SA account, rename it with different name and create a new user with a custom username.
USE Master;
ALTER LOGIN [sa] WITH NAME = [def_sa_act]
Physical Access Restriction: Control physical access to the SQL Server to ensure that only authorized personnel can interact with it.
Administrator Account Renaming: Change the name of the Administrator account on the SQL Server computer to deter potential attackers.
Note: Disable BUILTIN\Administrators account
Guest Account Disabling: Deactivate the Guest account to prevent unauthorized access through it.
Auditing and Monitoring: Enable auditing and continuously monitor for unusual or suspicious activities, remaining vigilant against potential security breaches.
Multiprotocol Encryption: Implement multiprotocol encryption to protect data while in transit, guaranteeing its security during network transmission.
SSL Configuration: Configure SSL (Secure Sockets Layer) for encrypted communication between clients and the SQL Server.
Firewall Configuration: Establish firewalls to govern and restrict network traffic to the SQL Server, permitting only authorized connections.
Isolation: Isolate the SQL Server from web servers, minimizing the potential attack surface.
By adhering to these measures, we can establish a comprehensive security framework that safeguards the SQL Server and the sensitive data it hosts.
Error: 18452, Severity: 14, State: 1. Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: xx.xx.xx.xxx]
This error may be due to some kind of loopback, check taking place which causes trusted connections via the loopback adapter to fail.
Workaround/Solution:
Option 1:
Loopback check can be removed by adding a registry entry as follows:
Edit the registry using regedit. (Start –> Run > Regedit )
Navigate to: HKLM\System\CurrentControlSet\Control\LSA
Add a DWORD value called “DisableLoopbackCheck”
Set this value to 1
If above doesn’t solve the issue, Please try with option 2:
Option 2:
we need to create SPNs for SQL Service account. While searching for SETSPN.exe command I came across this nice tool which can help a lot of pain like syntax error etc. is called as “Microsoft® Kerberos Configuration Manager for SQL Server®” which can be downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=39046
This tool can help in finding missing SPN and provide script to run or fix it directly, if you have permission. Basically, it can
Gather information on OS and Microsoft SQL Server instances installed on a server.
Report on all SPN and delegation configurations on the server.
Identify potential problems in SPNs and delegations.
Script:
--check if this is a PRIMARY or Secondary Replica of an AOAG
IF (SELECT HARS.role_desc from sys.dm_hadr_availability_replica_states HARS inner join sys.availability_groups ag on HARS.group_id = ag.group_id
where ag.name = '<AG_Name>'
and HARS.is_local = 1) = 'PRIMARY'
BEGIN
/*Replace with your own statements here*/
UPDATE STATISTICS <databaseName>.dbo.<table_name> WITH FULLSCAN, COLUMNS
END
ELSE
BEGIN
RETURN;
END
Script:
DECLARE @role_desc varchar(20)
IF EXISTS (select 1 from sys.dm_hadr_availability_replica_states)
BEGIN
IF( HAS_PERMS_BY_NAME ('sys.dm_hadr_availability_replica_states', 'OBJECT', 'execute') = 1)
BEGIN
-- if this is not an AG server then return 'PRIMARY'
IF NOT EXISTS( SELECT 1 FROM sys.DATABASES d INNER JOIN sys.dm_hadr_availability_replica_states HARS ON d.replica_id = hars.replica_id)
SELECT @role_desc = 'PRIMARY'
ELSE
-- else return if there is AN PRIMARY availability group PRIMARY else 'SECONDARY
IF EXISTS( SELECT hars.role_desc FROM sys.DATABASES d INNER JOIN sys.dm_hadr_availability_replica_states HARS ON d.replica_id = HARS.replica_id WHERE HARS.role_desc = 'PRIMARY' )
SELECT @role_desc = 'PRIMARY'
ELSE
SELECT @role_desc = 'SECONDARY'
END
ELSE
SELECT @role_desc = 'SQL Server is not configured for AOAG(Always ON High Availibility)'
SELECT @role_desc
END
ELSE
SELECT 'SQL Server is not configured for AOAG(Always ON High Availibility)'
Use this query to find available vs allocated CPUs on your server for Microsoft SQL Server
Query:
-----------------------------------------------------------------------------
-- CPU Allocation CHECK for MS SQL Server
-----------------------------------------------------------------------------
DECLARE @AvailableCpuCount int
DECLARE @AllocatedCpuCount int
SELECT @AvailableCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'
SELECT @AllocatedCpuCount = cpu_count FROM sys.dm_os_sys_info
SELECT @AllocatedCpuCount AS 'ASSIGNED CPUs for SQL Server #', @AvailableCpuCount AS 'Total CPUs on System #',
CASE
WHEN @AvailableCpuCount < @AllocatedCpuCount
THEN 'You are not using all CPUs assigned to Operating System. If it is a Virtual Machine, review your VM configuration to make sure you are not maxout Socket'
ELSE 'You are using all CPUs assigned to Operating System. Looks GOOD!'
END as 'CPU Usage Desc'
-----------------------------------------------------------------------------
GO
Default Trace Files: Most of the Database Administrators are surprised to learn that SQL Server 2005 and later versions maintain a constant default trace, operating around the clock, seven days a week. In the early stages of our careers, we are typically advised to utilize extended tracing sparingly. Before hastily deactivating the default trace on all your SQL Servers, it’s crucial to pause and understand precisely what information it is logging for us. The SQL Server default trace captures 32 unique events across 6 categories.
How to find default trace file is running?
You can use the following query to check if the default trace is currently running on your server.
-- Is the default trace running
SELECT*
FROM sys.configurations WHERE configuration_id = 1568
If default trace files are disabled, you can enable those by this query as follows:
Query:
-- Turn on advanced options
sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
-- Turn on the default trace
sp_configure 'default trace enabled',1;
GO
RECONFIGURE;
GO
Once, you enable default trace files, how to find the default trace file locations on your system? Use the below query to find the location.
Once you find the trace file location. How Can I View the Trace File Information?
Use this script to find the complete details which are captured in the trace file. You can filter out the details based on the categories using specific category ID.
Once you find the trace file location, you have to load (import) data from trace file into a table to read using this script as follows:
If you cannot find the details with the time at which those changes occurred. Go to the trace files location and run the same script on other trace files as well.
There are two methods by which we can easily find who made changes to the database objects in SQL Server as follows:
1. Using built-in (Standard Reports) – Schema Changes History Report.
2. To load the SQL Server Default Trace into a table to find changes made to the database objects.
Note: In both methods, you would be able to find the details only till your default trace files are not overwritten with new changes and default trace files should be enabled to capture these changes. Please refer to this post to find if default trace files are enabled or how to enable these.
Method 1:
1. Open SQL Server Management Studio
2. Connect to the SQL Server Instance.
3. Right click SQL Server Instance and Select Reports -> Standard Reports -> Schema Changes History as shown in the below snippet.
4. This will open up Scheme Changes History report which will have the details about who made changes to the SQL Server Database objects along with the detailed timestamp and Login Information. Sample report for Schema Change History is shows as follows:
Method 2:
Identify database schema changes using Using Default Trace Files.
Default Trace Files: Most of the Database Administrators are surprised to learn that SQL Server 2005 and later versions maintain a constant default trace, operating around the clock, seven days a week. In the early stages of our careers, we are typically advised to utilize extended tracing sparingly. Before hastily deactivating the default trace on all your SQL Servers, it’s crucial to pause and understand precisely what information it is logging for us. The SQL Server default trace captures 32 unique events across 6 categories.
Execute the below query to find the default path of trace file in SQL Server.
SELECT
path AS [Default Trace File]
,max_size AS [Max File Size of Trace File]
,max_files AS [Max No of Trace Files]
,start_time AS [Start Time]
,last_event_time AS [Last Event Time]
FROM sys.traces WHERE is_default = 1
GO
Once you find the trace file location, you have to load (import) data from trace file into a table to read using this script as follows:
If you cannot find the details with the time at which those changes occurred. Go to the trace files location and run the same script on other trace files as well.
Script:
USE tempdb
GO
IF OBJECT_ID('dbo.DBTraceTable', 'U') IS NOT NULL
DROP TABLE dbo.DBTraceTable;
SELECT * INTO DBTraceTable
FROM ::fn_trace_gettable
('E:\Program Files\Microsoft SQL Server\MSSQL15.ARUNSQL\MSSQL\Log\log_79.trc', default)
GO
SELECT
DatabaseID
,DatabaseName
,LoginName
,HostName
,ApplicationName
,StartTime
,CASE
WHEN EventClass = 46 THEN 'Object Created'
WHEN EventClass = 47 THEN 'Object Dropped'
ELSE 'NONE'
END AS EventType
FROM tempdb.dbo.DBTraceTable
WHERE DatabaseName = 'ARUNSQL'
AND (EventClass = 46 /* Event Class 46 refers to Object:Created */
OR EventClass = 47) /* Event Class 47 refers to Object:Deleted */
GO
As evident in the preceding snippet, event class 46 signifies the timestamp of database object creation, complete with the user responsible for its creation. In parallel, event class 47 denotes the timestamp of database object deletion, accompanied by the user responsible for deleting the database object.
Datapedia @JM_Arun 