SQL Server “Alias” name configuration

Today, My Dev team was looking for a solution where they wanted to access a Development server with alternate name of server instead of server’s original name because in an application connection string was using server name and team was not interested to change application configuration file. With SQL Server Configuration Manager there is a feature named as “Alias” – its a simply an alternative name given to the server. In other case this alias name can be used as easier for users to remember server instead of having a complicated name like DESKTOP-0P6BOHT ( for example my laptop name) , here we can simplify it to something like Virendra. I hope this approach can be applied in case of any server movement/migration from one server to another old/new server. Once an alias has been created and all relevant system objects have been updated to reference the alias, renaming or moving the server becomes a much less tedious process as only the alias need to be updated to reference the new server name and it can save huge time.

In order to configure an alias, Here I am using my PC, which name is “DESKTOP-0P6BOHT”

Step 1 ) Open SQL Server Configuration Manager and Select Alias from SQL Native Client Configuration

Step 2) A new dialog box “Alias – New” will be open

Set Alias Name as per your convenience (example – Virendra) , Your Server SQL port Number – here is default 1433 and Server Name as per your system name ( here my PC name)

Click on Apply /OK.

Great, now we should be able to connect to the instance of SQL Server using the newly created alias.  To check this Launch SQL Server Management Studio and enter the alias name as the server and try to connect.

Good News, you are connected with your server, this approach is harmonious if you are testing on same server, but if you want to access your server remotely, needs to add alias name in your organization’s DNS server.

SQL Server Hardening

Hi, As DBA, its many time we have to faced Questions from Auditors or Clients where your SQL Server follow SQL Server Hardening best practices?, Here are some few as,

• During installation, Install only required components, When the SQL Server installation is complete, harden the SQL Server environment.
  
• After the installation, use the SQL Server Configuration Manager tool in order to disable unnecessary features and services.
  
• Install the most recent critical fixes and service packs for both Windows and SQL Server.
  
• When you’re selecting authentication modes, Windows Authentication is a more secure choice
  
• If there is still a need to use SQL Authentication – enforce strong password policy.
  
• Do not use the SA account for day-to-day administration, logging on to the server remotely, or having applications use it to connect to SQL. It is best if the SA account is disabled and renamed.
  
• Create a role-based security policy with the Security Configuration Wizard tool.
  
• Create policies with Policy Based Management enable/ disable unnecessary features and services.
  
• After the server has been hardened, periodically asses the server’s security using the MBSA (Microsoft Baseline Security Analyzer) and SQL Server BPA (Best Practices Analyzer).
  
• For production SQL Servers running mission-critical databases, either hide the instance or disable the SQL Server Browser service.
  
• Change the default ports associated with the SQL Server installation to put off hackers from port-scanning the server.
  
• Enable a firewall to filter unnecessary and unknown traffic.
  
• At the very least, set security auditing to failed login attempts; otherwise, both failed and successful logins should be captured and monitored.
  
• If upgrading previous versions of SQL Server, remove the BUILTIN/Administrators group from the SQL Server Logins.
  
• Use the IIS Lockdown and URL Scan tools to harden IIS.
  

SQL Server Network Interface/Protocol

The SQL Server Network Interface (SNI) is a protocol layer that establishes the network connection between the client and the server. It consists of a set of APIs that are used by both the database engine and the SQL Server Native Client i.e SNAC

SQL Server has support for the following protocols:

➤
Shared memory: Simple and fast, shared memory is the default protocol used to connect from a client running on the same computer as SQL Server. It can only be used locally, has no configurable properties, and is always tried first when connecting from the local machine means The limitation is that the client applications must reside on the same machine where the SQL Server is installed.

➤
TCP/IP: TCP/IP is the most commonly / the most popular and common protocol widely used throughout the industry today. It communicates across interconnected networks and is a standard for routing network traffics and offers advanced security features.It enables you to connect to SQL Server by specifying an IP address and a port number. Typically, this happens automatically when you specify an instance to connect to. Your internal name resolution system resolves the hostname part of the instance name to an IP address, and either you connect to the default TCP port number 1433 for default instances or the SQL Browser service will find the right port for a named instance using UDP port 1434.

➤
Named Pipes: This protocol can be used when your application and SQL Server resides on a local area network. A part of memory is used by one process to pass information to another process, so that the output of one is the input of the other. The second process can be local (on the same computer as the first) or remote (on a networked computer). TCP/IP and Named Pipes are comparable protocols in the architectures in which they can be used. Named Pipes was developed for local area networks (LANs) but it can be inefficient across slower networks such as wide area networks (WANs). To use Named Pipes you first need to enable it in SQL Server Configuration Manager (if you’ll be connecting remotely) and then create a SQL Server alias, which connects to the server using Named Pipes as the protocol. Named Pipes uses TCP port 445, so ensure that the port is open on any firewalls between the two computers, including the Windows Firewall.

➤
VIA: Virtual Interface Adapter is a protocol that enables high-performance communications between two systems. It requires specialized hardware at both ends and a dedicated connection. Like Named Pipes, to use the VIA protocol you fi rst need to enable it in SQL Server Configuration Manager and then create a SQL Server alias that connects to the server using VIA as the protocol. This protocol has been deprecated and will no longer be available in the future versions of SQL Server.

Regardless of the network protocol used, once the connection is established, SNI creates a secure connection to a TDS endpoint

TDS Endpoint : Tabular Data Stream (TDS) Endpoint also known as TSQL.

For Details About SQL Server ENDPOINT , Very good explained blog is http://www.tobuku.com/docs/SQL%20Server%20Endpoint.pdf , Thanks for writer.