Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,039,818 hits

  • Select GETDATE()

    June 2011
    M T W T F S S
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  

Archive for June, 2011

« Previous Entries

Help : Where is SQL Server ErrorLog?

Posted by blakhani on June 26, 2011


While helping unknown faces via MSDN SQL Server Forum, I have asked many times to share the “SQL Server ErrorLog”. Most of the time, I need to explain where the ErrorLog(s) are located. so this post is to help those who are new to SQL Server. Well, if you ever call Microsoft SQL Support for any problem with SQL, they (including me) would ask SQL ErrorLog.

You may ask “What is ErrorLog file?”
SQL Server maintains its own error logs that contain messages describing informational and error events. I personally don’t like name of the file “ErrorLog” as this file does not contains only errors, it also contains information messages. This is the reason we have added additional text in information message called “This is an informational message only. No user action is required”.

  • Location of Errorlog when SQL Server is running and you are able to connect:
    • Connect to SQL Server using SQL Server Management Studio by providing correct name. To find the name to connect, refer my earlier post
    • In Query window run below command
      sp_readerrorlog
    • Here is the output

    ERRORLOG

    • Highlighted area “Logging SQL Server messages in file ‘D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\Log\ERRORLOG’” is current ERRORLOG file 
    • If you look at folder D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\Log\ here are files.

                          LOG Folder

          As you can see above that LOG folder contains many files. I have provided little information about them in picture.

  • Location of Errorlog when SQL Server is running and you are NOT able to connect:

    There could be situations where you are not able to connect to SQL Server because SQL Server service is not getting started. In those situations, sp_readerrorlog can’t be run. Now, we have to find ERRORLOG file using other technique. Here we go.

    • Open SQL Server Configuration Manager:
      Go to Start > All Programs > Microsoft SQL Server 2005 (or 2008) (or 2008 R2) > Configuration Tools > SQL Server Configuration Manager
      SQL Server Configuration Manager
    • Once you open Configuration Manager, you would get below screen. There you need to locate your SQL Server Instance, right click and properties.

      Configuration Manager

    • Here is the Properties window. Come to Advanced tab & Click on drop down for “Startup Parameters” value and you would see three parameters (can be more also). As shown below there would be –d, –e and –l parameters. Value after –e is your SQL Server Error Log location.

      SSCM Properties

Now if someone is asking ERRORLOG file, please provide using above two methods. You can also get it from SQL Server Management Studio, as described here 

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Advertisement

Posted in Configuration Manager, ERRORLOG, SQL Server | 40 Comments »

Help : How can I hide databases from users?

Posted by blakhani on June 25, 2011


This is one of the common questions I have seen on forums, most common request from administrators who have been given task to create multi-tenant SQL Server. This essentially means various department of company or various client of the hosting company would access same SQL Server Instance. Business owners demand that they want clients to see only those databases which are relevant for them. I have worked with a company where the database name was name of the client. Inability to hide name of databases would essentially mean exposing information about their other clients.

In simple words, if Contoso is a database hosting company who has “Northwind traders” and “TailSpin Toys” as their client. If TailSpin logs in to the SQL Server, they should not see “Northwind” database and vice versa.

SQL Server Enterprise Manager calls the sp_MSdbuseraccess stored procedure to determine the accessibility of each database. The sp_MSdbuseraccess stored procedure then runs against every database in SQL Server to determine the user’s permission level in that database. So this can be achieved by altering the stored procedure as documented in KB http://support.microsoft.com/?id=889696 (You may experience a slow response from SQL Server Enterprise Manager when many databases exist in an instance of SQL Server)

In SQL 2005 onwards we fire queries to get the information in SQL Server Management Studio (a.k.a. SSMS) . Now, SQL Server 2005 onwards, we also have limited metadata visibility which I am going to use as “trick” to achieve our goal. As per http://msdn.microsoft.com/en-us/library/ms178534.aspx
{

If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.
}

So, here is the quick script which can be used to demo this. If you love SSMS then I have also given instruction.

  • Create Two databases.
Create Database [NorthWindDB]
go
Create Database [TailSpinDB]
go
  • Create Logins (Not mapped to any Database)
CREATE LOGIN [NorthWindLogin] WITH
PASSWORD=‘E@syP@ssw0rd’
go
CREATE LOGIN [TailSpinLogin] WITH
PASSWORD=‘E@syP@ssw0rd’
go
  • Right Click on the Root section of the SSMS Tree (SQL Server Name)>Properties>Permissions>Click on the account, and select Deny to view databases. OR use below T-SQL
REVOKE VIEW ANY DATABASE TO [NorthWindLogin]
go
REVOKE VIEW ANY DATABASE TO [TailSpinLogin]
go
  • Right Click on the newly created databases > Properties > Files, and change the Owner to the newly created accounts. OR use below T-SQL
use [NorthWindDB]
go
sp_changedbowner 'NorthWindLogin'
go
use [TailSpinDB]
go
sp_changedbowner 'TailSpinLogin'
go

Here is the final out come…

SSMS

NorthWindLogin can see only NorthWindDB database and TailSpinLogin can see only his database.

Erland Sommarskog, SQL MVP, has filed request “Need VIEW DEFINITION permissions per database”. If you feel this is useful then please vote here!

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Uncategorized | 3 Comments »

Help : What’s my SQL Server Name?

Posted by blakhani on June 19, 2011


“What’s in a name? That which we call a rose by any other name would smell as sweet.” – William Shakespeare

Well, SQL Server connectivity did not know about Shakespeare Hot smileWhile working on the forum, one of the most common error posted is below:

TITLE: Connect to Server
——————————
Cannot connect to <Shakespeare>.
——————————
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
——————————
BUTTONS:
OK
——————————

Let’s look closely at the error message.
1. The server was not found or was not accessible.
2. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
3. error: 26 – Error Locating Server/Instance Specified.

All of the above are shouting out loud that “Damn! you have given wrong name to connect”. If you have given right name then check if SQL is up and running and then whole troubleshooting starts. The purpose of this post is to help the beginners who don’t care about troubleshooting because they are Visual Studio developers and they are connecting to local SQL Express most of the time.

Okay my dear developers, don’t worry… take a deep breath and start…

To get the SQL Server Name, the first thing you need to do is to find the instance name. On a single machine you can install 16 instances of SQL Servers. Each would have some name. To find the instance name of a SQL Server instance, please follow the steps below:

  • Open SQL Server Configuration Manager (SSCM) from Start – > All Programs -> Microsoft SQL Server 2005/2008/2008 R2 -> Configuration Tools.
  • In the SSCM, click SQL Server Services under SQL Server Configuration Manager (Local), you could find list like SQL Server (InstanceName),
  • The InstanceName is what we need to find, instance name. If you install multiple instances on the server, you will see more than one SQL Server (InstanceName) listed.

image

In above picture you can see two “SQL Server” in “Service Type” column. It means I have two instances of SQL Server. For default instance you would see Name as SQL Server (MSSQLServer). It means on my machine I have two “named” instances of SQL Server. Keep in mind that you can have only only default instance on a machine.

Okay great! Now you know that your machine has default instance or named instance. And if it’s named then you know its name. Correct? Now lets quickly see what name you should use to connect. Before that you need to find hostname or computer name. Go to Start > Run > cmd This would open command prompt. Type hostname and you should see your machine name as below.

image

Default Instance Named Instance Comments
BLAKHANI BLAKHANI\SQLEXPRESS
BLAKHANI\SQL2K8R2		 this would work from remote machine also.
(LOCAL) (LOCAL)\SQLEXPRESS
(LOCAL)\SQL2K8R2		 local only
. .\SQLEXPRESS
.\SQL2K8R2		 local only
LOCALHOST LOCALHOST\SQLEXPRESS
LOCALHOST\SQL2K8R2		 local only
BLAKHANI.MYDOMAIN.COM BLAKHANI.MYDOMAIN.COM\SQLEXPRESS
BLAKHANI.MYDOMAIN.COM\SQL2K8R2		 Remote connections should be allowed as mentioned in http://support.microsoft.com/kb/914277 (

How to configure SQL Server 2005 to allow remote connections)
TCP:221.222.223.224,1433 TCP:221.222.223.224,9999 IP and Port can be found by reading ERRORLOG of SQL Server.

 

If you don’t see configuration manager then you can also get the instance name from services applet. To do this, go to Start > Run > Services.msc
Here is what i see on my screen

image

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Connectivity, SQL Server | 58 Comments »

« Previous Entries