Archive for the Category ◊ MSSQL Server ◊

MSSQL WebAdmin ASP.NET ENT Manager in Plesk show Virus
Saturday, May 23rd, 2009 | Author:

We have recently had this complain from our various Shared hosting as well as Dedicated Server hosting clients that MSSQL Webadmin ASP .NET Enterprise Manager is having a virus / trojan on it. When the first complain arrived we too thought that there seems to be a problem with the server and might be MSSQL Webadmin ASP .NET Enterprise Manager has got an injection in it. But after receiving too many complains from most of our dedicated server clients we started investigating the problem and now we knew that this is something that has happen globally. After viewing MSSQL Webadmin ASP .NET Enterprise Manager on Plesk server it looked as the picture below:

more…

About MSSQL Server
Tuesday, March 17th, 2009 | Author:

The architecture of Microsoft MSSQL Server is mainly divided into three components:

1. SQLOS: which implements the basic services required by MS SQL Server, including thread scheduling, I/O stat management and memory management.

2. Relational Engine: which implements the relational database components including support for databases, tables, queries and stored procedures as well as implementing the type system.

3. Protocol Layer: which exposes the MS SQL Server functionality.

SQLOS

more…

Windows Clustering and Load Balancing, Explained
Monday, March 16th, 2009 | Author:

Clustering in Windows 2003 Dedicated or Shared hosting is a means of providing High Availability to your applications and websites. Clustering is a group of machines acting as a single entity to provide resources and services to the network. In time of failure, a fail over will occur to a system in that group that will maintain availability of those resources to the network. You can be alerted to the failure, repair the system failure, and bring the system back online to participate as a provider of services once more. You learn about many forms of Windows clustering. Clustering in Windows 2003 hosting can allow for failover to other systems and it can also allow for load balancing between systems. Load balancing in Windows 2003 hosting is using a device, which can be a server or an appliance, to balance the load of traffic across multiple servers waiting to receive that traffic. The device sends incoming traffic based on an algorithm to the most underused machine or spreads the traffic out evenly among all machines that are on at the time. A good example of using this technology would be if you had a web site that received 2,000 hits per day. If, in the months of November and December, your hit count tripled, you might be unable to sustain that type of increased load. Your customers might experience time outs,
more…

Fix for MSDTC Service errors in Windows Cluster
Thursday, March 12th, 2009 | Author:

Some times Configuring MSDTC and their error can ruin your Windows Cluster service and your time as well. You may get one of the following error when you failover the MSDTC service from one Dedicated node to another.

This would only happen if the Cluster services has been installed before installing and configuring MSDTC Service. Hence it is highly recommended that you first install and configure MSDTC and then configure the Windows Cluster Service.

Event ID: 4097
Description:
MS DTC started with the following settings: Security Configuration (OFF = 0 and ON = 1): Network Administration of Transactions = 1, Network Clients = 0, Distributed Transactions using Native MSDTC Protocol = 1, Transaction Internet Protocol (TIP) = 0, XA Transactions = 1.

OR

Event ID: 4395
Description:
MSDTC detected that MSDTC related information in the local registry is different from that in the shared cluster registry. Error Specifics: d:ntcomcom1xdtcsharedmtxclumtxclusetuphelper.cpp:541, CmdLine: C:WINNTSystem32msdtc.exe, Pid: 796
Data:
0000: 05 40 00 80 .@.?

OR

Event ID: 4384
Description:
MS DTC was unable to start because the installation was not configured to run on a cluster. Please run comclust.exe and restart MS DTC. Error Specifics: d:ntcomcom1xdtcsharedmtxclumtxclusetuphelper.cpp:668, CmdLine: C:WINNTSystem32msdtc.exe, Pid: 796

OR

Event ID : 7024
Source : Service Control Manager
Description: The MSDTC service terminated with service specific error 3221229584.

Initially you should try and run the command below and check if it solves the problem:

msdtc -resetlog

If that does not help then follow the fix below:

more…

Checklists for Windows Cluster:
Thursday, March 12th, 2009 | Author:

This checklist helps you prepare for installation. It is very important to go through all of them else it make it very difficult to restart if either one of them is missed or not configured:

Software Requirements

•    Microsoft Windows Server 2003 Enterprise Edition or Windows Server 2003 Datacenter Edition installed on all dedicated servers in the cluster.
•    A name resolution method such as Domain Name System (DNS), DNS dynamic update protocol, Windows Internet Name Service (WINS), HOSTS, and so on.

more…

Group Policies Overview for SQL Server
Friday, February 27th, 2009 | Author:

Group Policy is a core part of Microsoft’s IntelliMirror technology.You can use Group Policy to manage all aspects of the Server environment for Windows Server, including Registry settings, software installation, scripts, security settings, and so on.The possibilities of what can be done with Group Policy are almost limitless.With VBScript or Jscript, you can write entire applications to execute via Group Policy.You can install software automatically across the network and apply patches to applications. When deciding on the Group Policies you plan to enforce on your network, you should keep in mind that the more policies applied, the more network traffic, and hence the longer it could take for users to log onto the network. Group policies are stored in Active Directory as Group Policy Objects (GPO).These objects are the instructions for the management task to perform. Group Policy is implemented in four ways:

more…

How MS SQL Failover Clustering Work
Friday, February 27th, 2009 | Author:

The clustered nodes use a “heartbeat” signal to check whether each node is alive, at both the operating system level and the SQL Server level. At the operating system level, the nodes in the cluster are in constant communication, validating the health of all the nodes.

After installing a SQL Server failover cluster, the node hosting the SQL Server resource uses the Service Control Manager to check every 5 seconds whether the SQL Server service appears to be running. This “LooksAlive” check does not impact the performance of the system, but also does not do a thorough check; the check will succeed if the service appears to be running even though it might not be operational. Because the LooksAlive check does not do a thorough check, a deeper check must be done periodically; this “IsAlive” check runs every 60 seconds.

The IsAlive check runs a SELECT @@SERVERNAME Transact-SQL query against SQL Server to determine whether the server can respond to requests. Although a reply to the IsAlive query confirms that the SQL Server service is available for requests, it does not guarantee that all user databases are available, or that the user databases are operating within necessary performance/response-time requirements.

more…

Encrypt SQL 2005 database with Certificates
Thursday, February 19th, 2009 | Author:

Using Certificates to Encrypt Data

Certificates are parallel with asymmetric keys in the SQL Server 2005 encryption hierarchy. A certificate is simply a method of using asymmetric encryption. Certificates bind public keys to individuals who hold the associated private key. Certificates use the same RSA algorithm as asymmetric keys; therefore, they are resource-intensive and their use is normally restricted to encrypting other keys. SQL Server contains an integrated certificate authority, which it uses to issue its own selfsigned, and industry standard X.509 certificates. Alternatively, you can import certificates from an external certificate authority.The use of external certificates allows you to use a wider range of key lengths, which can provide enhanced security. Certificates are the most secure way in which to encrypt data natively within SQL Server 2005.You can use the CREATE CERTIFICATE statement to create a certificate within SQL Server 2005.

more…

Secure MS SQL 2005 Database with EFS Encryption
Wednesday, February 18th, 2009 | Author:

EFS Encryption

EFS can be used to encrypt SQL Server 2005 data files and folders. EFS is supported on Windows 2000 and later operating systems with New Technology File Systems (NTFS) formatted drives. EFS uses a combination of symmetric and asymmetric methods to provide transparent SQL Server 2005 data encryption. On Windows 2003 Server and newer operating systems, EFS by default creates a random File Encryption Key, which is a 256-bit AES key to perform data encryption.The File Encryption Key is then itself encrypted with the user’s public key and stored within the encrypted file or folder.

To encrypt SQL Server 2005 data files and folders using EFS, follow these steps:

1. Stop the SQL Server service.
2. Log out and log in using the SQL Server service account credentials.
3. Right-click on the file or folder to be encrypted and select Properties | General Tab | Advanced.

more…

Importance and Ways to Secure SQL 2005 Database
Tuesday, February 17th, 2009 | Author:

Why Secure Data in your Windows MSSQL database?

Databases often contain sensitive financial, healthcare, and corporate data. As mentioned earlier, data security breaches are occurring at an alarming rate and international legislations have been passed, which set regulations on how organizations must protect this sensitive data. The Payment Card Industry (PCI), Health Insurance Portability and Accountability Act (HIPAA), Personal Information Protection and Electronic Documents Act (PIPEDA), Gramm-Leach-Bliley Act (GLBA), and the UK Data Protection Act are just a few of these regulations. Several regulations require that sensitive data be encrypted and that organization’s must identify and report data disclosure or misuse. If these regulations are not followed, organizations can face serious repercussions, ranging from financial penalties to imprisonment of responsible parties. Depending on the nature of your business, the above regulations may not apply, but before you discount the need to encrypt data

more…

Secure MS SQL – BEST PRACTICES
Monday, February 02nd, 2009 | Author:

There are several points that Microsoft suggests before configuring your MS SQL Server.

BEST PRACTICES ACCORDING TO MICROSOFT

  • Install only those components that you will use immediately. Microsoft recommends that you create a list of components that you will be using, and only enable those. If the need arises, you can install the additional components at that time. The components in a SQL Server installation are the Database Engine, Analysis Services Engine, Reporting Services, Integration Services, Notification Services, and Documents and Samples. more…
Difference between SQL Server 2005 and SQL Server 2008
Wednesday, January 14th, 2009 | Author:

difference-between-2005-2008

MS SQL 2005 Server was released after 5 servers of it previous SQL version, MS SQL 2000, Hence it required allot to revisions to cope up with the current windows development environment. Therefore MS SQL 2005 SP2 had major changes in it, with too many performances and security fixes. Microsoft could not add few revisions due to its basic development structure however they had all the scope to introduce them in SQL 2008 release. And indeed, MS SQL 2008 has many installation as well as performance fixes being applied to it and allowing Windows Administrators to have full control on SQL activities. The biggest advantage of SQL 2008 over SQL 2005 is the ability to manage and maintain server performance. SQL 2008 does not require too much resource which is the best deal for today’s Shared Hosting environment. Also an upper hand to whose to do not want to invest too much on hardware of their dedicated servers just to run SQL server on it.

more…

close Port 445:

  • Click Start >> Run, to open the Run dialog box
  • Here, type regedit to open the registry.
  • Navigate to the following registry key – HKEY_LOCAL_MACHINE >> System >> CurrentControlSet >> Services >> NetBT >> Parameters
  • On the right-hand pane find the option TransportBindName.
  • Double
    click
    on
    TransportBindName and delete the existing default value.

  • click
    Ok

From the above, it is clear, that you have closed Port 445 by giving a blank value to TransportBindName for NetBT services.

close Port 135:

  • Click Start >> Run, to open the Run dialog box
  • Here, type regedit to open the registry
  • Navigate to the following registry key – HKEY_LOCAL_MACHINE >> software >> microsoft >> Ole
  • On thee right hand window pane find an option called EnableDCOM
  • Double-click EnableDCOM and change the value from Y to N
  • click Ok
  • Close the Registry Editor and restart your computer

These steps will only work for a standalone servers. Any serveres that are in a cluster state such as Active Directory, SQL failover cluster, Network Load Balancing [NLB] or Windows Replication service that NEVER-EVER follow these steps as it will simply diable port 135 which is used my Distributed File System [DFS] for the servers to comunicate with each other. Disabling it will just wont allow the servers to communicate and the services will fail.

Category: ASP.NET Web Hosting, MSSQL Server, Window Hosting |  Comments off
Secure your MS SQL Database Connection String
Tuesday, November 11th, 2008 | Author:

Ways to secure your MS SQL connection string in ASP .NET.

We have seen allot of people looking to have the best option to secure the connection string in their ASP .NET code to connect the MS SQL database since it contains the username and password of their database. It is very important to use a secure method for corporate clients and those who save Credit Card details in their MS SQL database. Or they will easily get hacked and all the important data will be exploited by the hacker. And also for those who store important data in MSSQL.

Here are the list of methods that can be used to secure your MS SQL connection string in your ASP.NET application.

METHODS:

1. Using a DSN connection string:

more…

Enable SQL 2005 Remote Connections
Monday, November 10th, 2008 | Author:

Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition

You must enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:

1.    Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click MS SQL Server Surface Area Configuration.
2.    On the MS SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3.    On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.

Note Click OK when you receive the following message:
Changes to Connection Settings will not take effect until you restart the Database Engine service.
4.    On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Enable the SQL Server Browser service

If you are running MS SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you must enable the SQL Server Browser service to allow for remote connections. For example, MS SQL Server 2005 Express is installed with a default instance name of Computer Name\SQLEXPRESS. You are only required to enable the SQL Server Browser service one time, regardless of how many instances of SQL Server 2005 you are running. To enable the MS SQL Server Browser service, follow these steps.

more…

Using Pass Phrases to Encrypt Data in MSSQL 2005
Wednesday, October 22nd, 2008 | Author:

Using Pass Phrases to Encrypt Data

A password that allows for spaces can be referred to as a pass phrase. The benefit of pass phrases is that you can make them meaningful and easy to remember. Instead of creating and managing encryption keys or certificates in your database server, you can encrypt data using only a pass phrase.The ENCRYPTBYPASSPHRASE statement uses the supplied pass phrase to generate a symmetric key, which is used to perform the actual data encryption. No key management is required, as the key will be recreated each time the same pass phrase is supplied.The common syntax of the ENCRYPTBYPASSPHRASE statement is as follows:

ENCRYPTBYPASSPHRASE ('PASSPHRASE', 'PLAINTEXT')

In this statement, PASSPHRASE specifies the data string to be used to derive an encryption key. PLAINTEXT specifies the data to be encrypted. No permissions are required to run the ENCRYPTBYPASSPHRASE statement.

The following syntax encrypts the string using the supplied pass phrase:

more…

Some times we see the error below in the event logs:

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [T:\MSSQL\DATA\%file_name%] in database [%DB_name%] (2).  The OS file handle is 0×00000838.  The offset of the latest long I/O is: 0x000000ebdc0000

Do you refer it to as a stalled IO? We always hear a questions such as, what’s wrong with SQL Server..? Why is SQL taking so long to read or write to the disk..? Let us discuss why it is not a MS SQL Server problem:

MS SQL Server does data file reads and writes almost exclusively as asynchronous IO, using the win32 APIs ReadFile, WriteFile, ReadFileScatter and WriteFileGather. Each of these APIs behave in a fairly similar steps, the caller sends in a handle to the file, some memory location to read or write, the size of the block and a structure that tells the kernel how to handle the IO. In MS SQL Server’s case, how to handle the IO is Asynchronously, please.  The call returns immediately so that the thread issuing so that the IO can get out of the way and make life happy for other users who are also waiting got their query to return.

The catch here is that, ordinarily the time between the Asynchronous call to read or write and the completion of the read or write should be on the order of 10ms. The longer it takes for an IO to return the more noticeable a performance impact there is to end users.

more…

Category: MSSQL Server, Window Hosting |  Tags: , , , , , | Comments off