If you are running your databases on a Windows network environment, the best practice is to use Windows Authentication rather than Mixed Authentication (Both Windows and SQL Authentication). However, there are many companies that still use Mixed Authentication which allows both AD accounts and SQL Accounts. If your organization MUST use SQL authenticated accounts (SQL Logins), you must ensure that your SA account is locked down as completely as possible and that only key people in the team know the password.
The most obvious first steps is to utilize a complex passphrase, create separate accounts for each DBA, and then ensure that nothing ever uses the SA account.
However, there is still an issue to resolve with someone attempting to hack into your SQL Server by using a brute force attack against the SA account. Just like the Administrator account in Windows, the SA account can NOT be locked out, regardless of the number of failed login attempts.
Being a smart DBA or a System Admin, you can defeat the brute force attacks on the Administrator (SA) account in a very simple way and that is by simply renaming the Administrator account of your SQL Servers. Once you change the SA account, hackers will fail when using SA account (default SA account) when trying to connect using SA account since the account is now renamed or is invalid.
Let’s take a look at the steps required to rename the default SA account: Note: Renaming an SA account was not possible prior to SQL Server 2005.
Renaming SA Account
ALTER LOGIN SA WITH NAME = [SALEEM]
GO
Once the above step is performed (i.e. SA account has been renamed), let’s disable the SA account:
Securing SQL Server is one of the top priorities of any DBA. It is important for DBA’s to make sure that the database server is TIGHTLY SECURED and is not exposed to users that do not need access to SQL Server. Only valid and approved users MUST be granted required permissions.
Apart from configuring the right security/permissions for SQL Server users/logins, it is also important to select the right account to run SQL Server services. In todays tip, we will explore when to use what service accounts for running SQL Server services.
When to use a Domain User Account for running SQL Server Service?
If your SQL Server interacts with other servers, services or resources on the network (ex: Files Shares, etc.) or if your SQL Server services uses linked servers to connect to other SQL Servers on the network, then you may use a low privileged domain user account for running SQL Server services. Domain user account is the most recommended account for setting up SQL Server services that interact with other servers on the network. One of the plus points of using a Domain User Account is that the account is controlled by Windows active directory therefore, domain level policy on accounts apply to SQL Server service account as well.
When to use a Network Service Account for running SQL Server Service?
NEVER should you use Network Service Account for running SQL Server services. Network Service accounts are shared with other services running on the local computer. Network Service Account is a built-in account that has more access to server resources and objects than users accounts of local user groups.
Any SQL Server services that runs on Network Service Account, can access network resources by using the credentials of the computer account. This account shows up as NET AUTHORITY\NETWORK SERVICE when configuring SQL Server Services.
When to use Local System Account for running SQL Server Service?
NEVER should you use local system account for running SQL Server services. Local System Account has more permissions than you would think. It is a very high-privileged built-in account created by Windows O/S.
Local System Account has extensive privileges on the entire local system and acts as a computer on your company’s network. This account shows up as NT AUTHORITY\SYSTEM when configuring SQL Server services.
When to use Local User Account for running SQL Server Service?
If your SQL Server DOES NOT interact with other servers, services or resources on the network (ex: Files, Shares, Linked Servers, Etc.) then you may use a low privileged local user account for running SQL Server Services.
NOTE: You are not required to use the local user account with administrative privileges to run SQL Server services.
What would you do when an object that is referenced in several places and in several databases (Stored Procedures, Functions, etc.) is being moved to a different database or is being renamed due to a new object naming policy? Think of how much work this change would generate for you?
Wouldn’t it be nice if there was a way to take care of these changes automatically?
A synonym is an alternative name (think of it like an alias) for objects in SQL Server that provides a single-part name to reference a base object that uses long object names, a two-part, three-part, or even for-part names object names.
Synonyms can reference objects that exist on local or remote servers. They provide a layer of abstraction to protect client application from any changes made to the name or the location of the base underlying object. The binding between a synonym and its underlying object is strictly by name only which means the underlying object can be modified, dropped or dropped and replaced by another object. You can also perform any DML operations on a synonym which actually gets performed on the underlying table.
Synonyms can be very helpful in the above scenario. Once you create synonyms for objects, you can move or rename your base object without affecting its reference or use.
Synonyms can also be used for creating short-cuts for long object names or the object names used in four-part queries for example linked servers. (ex: ServerName.DatabaseName.OwnerName.ObjectName)
Synonym can be created on the following object types:
User tables (both permanent and temporary)
Views
Stored Procedures (T-SQL and CLR)
Extended Stored Procedures
Replication Stored Procedures
Replication Filter Procedures
Assembly Aggregate Functions (CLR)
Assembly Table Valued Functions (CLR)
Scalar Functions
Inline Table Valued Function, etc.
Lets now look at some of the examples on how to use Synonyms in SQL Server.
Creating a Synonym for an object:
CREATE SYNONYM CUST_ADD
FOR ADVENTUREWORKSLT2008R2.SALESLT.ADDRESS
GO
Query newly created Synonym:
SELECT * FROM [CUST_ADD]
GO
Deleting a Synonym:
DROP SYNONYM [CUST_ADD]
GO
Let’s now create a Synonym for a Linked Server object. Make sure the linked server is already established. Once the Synonym is created for Linked Server, you can refer to the linked server object using the Synonym (short name)
--CREATE A SYNONYM ON A LINKED SERVER OBJECT
CREATE SYNONYM CUST_ADD
FOR SQLTIPS.ADVENTUREWORKSLT2008R2.SALESLT.ADDRESS
GO
--QUERYING THIS SYNONYM
SELECT * FROM [CUST_ADD]
GO
--DROPPING THIS SYNONYM
DROP SYNONYM [CUST_ADD]
GO
Let’s now look at inserting data in to a table using a Synonym:
INSERT INTO [CUST_ADD] VALUES
(
'ONE WAY'
,'MICROSOFT WAY'
,'REDMOND'
,'WASHINGTON'
,'USA'
,98052
,NEWID()
,GETDATE()
)
Go
In the database mirroring environment Principal interacts with all the users but the mirror server only receives transaction log data from the principal server and other than that mirror database doesn’t do anything as its in RECOVERING state all the time. In this knowledge nugget, we will explore how you can use Mirror database for Reporting or Querying purpose.
What are Database Snapshots?
Database snapshot is a static, read-only, transaction-consistent snapshot of its source database as it existed at the moment of the snapshot creation. With Database Snapshots you can take advantage of the mirror database for high availability purposes to offload reporting. You can create a database snapshot on the mirror database and direct client connection requests to the most recent snapshot. You can create a database snapshot on the mirror database only when the database is fully SYNCHRONIZED.
As long as both mirror and principal server are communicating with each other, database snapshots can be accessible to clients. Keep in mind that as database snapshot is static, new data is not available. You must create new database snapshots periodically and have applications direct incoming client connections to the newest snapshot.
The new database is almost empty, but it grows over time as more and more database pages are updated for the first time. Because every snapshot on a database grows incrementally in this way, each database snapshot consumes as much resources as a normal database. Depending on the configurations of the mirror server and principal server, having an excessive number of database snapshots on a mirror database might decrease performance on the principal database. Therefore, it is recommended that if you can live without touching the mirror databases / servers, great and if not you can keep only a few relatively recent snapshots on your mirror databases.
What Happens During a Role Switch?
If role switching occurs, the database and its snapshots are restarted, temporarily disconnecting users. Afterwards, the database snapshots remain on the server instance where they were created, which has become the new principal database.
Users can continue to use the snapshots after the failover. However, this places an additional load on the new principal server. If performance is a concern in your environment than it is recommended that you create a snapshot on the new mirror database when it becomes available and redirect clients to the new snapshot, and drop all of the database snapshots from the former mirror database.
Creating a Database Snapshot of a user database
Lets create a snapshot on the mirror database, make sure you are connected to the mirror database:
USE MASTER;
CREATE DATABASE SQLCOMMUNITY_SnapSHOT_0400
ON (Name= 'SQLCOMMUNITY_Data', --This is the logical name of the data file of SQLCOMMUNITY database
FILENAME='H:\MSSQL\DATA\SQLCOMMUNITY_0400&.snp')
AS SNAPSHOT OF SQLCOMMUNITY
Go
You are now ready to use database snapshots for querying mirror databases.
How and where to view database snapshots?
In the object explorer, connect to the instance of Microsoft SQL Server and expand Databases and then expand Database Snapshots and select the snapshot you want to view.
Dropping a Database Snapshot
Once you are done working with database snapshots, you may drop the database snapshots the same way as you would any user databases:
-- Deleting a snapshot database
Use Master;
Drop Database SQLCOMMUNITY_SnapSHOT_0400
Go
I often get this question from the community and my answer to this question depends based on your network environment whether you are on a local computer with no networks or whether you are on a Local Area Network or whether you are on a Wide Area Network. I’ve tried to provide some guidance / recommendation below that works best for many SQL Server installations.
In order to connect to SQL Server, your client machine must have a network protocol enabled that can communicate with SQL Server database engine. By default, SQL Server can listen on 4 different protocols that come installed with SQL Server. However, not all the installed protocols will be enabled and you would need to enable them at the server using SQL Server Configuration Manager to be able to use them from your client machine.
The four network Protocols in SQL Server are:
Protocol
Description
Shared Memory
This is the simplest protocol of all the other protocols available in SQL Server. The limitation is that the client applications must reside on the same machine where the SQL Server is installed.
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
This is 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.
VIADo not use this as this is Deprecated.
Virtual Interface Adapter (VIA) protocol works with VIA hardware. This protocol has been deprecated and will no longer be available in the future versions of SQL Server.
Here are the Best Practices for using SQL Server Network Protocols:
What Protocol to use on a Local Server?
If you are running your server application on a local computer which also runs SQL Server 2008 then Shared Memory or Named Pipes protocol is your best choice. However, Named Pipes get loaded in kernel mode of the operating system and are much faster on the local machine.Note: This is only true if both the application and SQL Server are running on the same machine and if not then it would use Network Pipes that can be used over Local Area Networks.
What Protocol to use on a Local Area Network (LAN)?
As long as your Local Area Network is fast, both TCP/IP and Named Pipes would perform with the same speed. However, if there are more requests waiting to hit the server then Named Pipes may encounter pipe-busy error when trying to connect to SQL Server whereas, TCP/IP sockets doesnt experience this problem as it supports backlog queue for any number of requests.
What Protocol to use on a Wide Area Network (WAN)?
Data transmission using TCP/IP sockets is much more streamlined and has relatively less overhead compared to other network protocols. TCP/IP protocol can also take advantage of many performance features supported by the operating system that includes windowing, delayed acknowledgement which can be very helpful on slow networks.
Recent Comments