Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,153,615 hits

  • Select GETDATE()

    December 2025
    M T W T F S S
    1234567
    891011121314
    15161718192021
    22232425262728
    293031  

Posts Tagged ‘sql server’

« Previous Entries
Next Entries »

UDL Test Series – Part 1 – A Basic Connectivity Check

Posted by blakhani on March 18, 2014


In most of the situations you may not have SQL Server Management Studio (SSMS), command line tools (SQLCmd, OSql, Isql) to check whether you are able to connect from given client to SQL Server or not. If you have ever called Microsoft SQL Support team for connectivity issue, “UDL Test” would be familiar to you.

UDL stands for Universal Data Link. Its “universal” means it is not just to test SQL Server connectivity test, but it works for other RDBMS as well. We can also get connection string using UDL file. Lets start with connectivity first.

To create UDL file, Right Click anywhere on desktop > New > Create a empty Text Document file.

image

Once you have text file, change the extension of the file to udl. A warning might appear, explaining that changing file extensions could cause files to become unusable, hit OK. In case you don’t see file extension: Open Windows Explorer, and on the Tools menu, click Folder Options. On the View tab, clear the Hide file extensions for known file types check box and then click OK.

As soon as you would change the extension icon would change as below.

image

Now, double click on the file and on the first tab “Provider”, choose appropriate provider. Since I am going to test connectivity to SQL, I have used “SQL Server Native Client 11.0” provider and hit “next”

image

Next tab is “Connection” which is about the login credentials. Think of the SSMS login screen and it has all the details asked. We can choose Windows/SQL Login. Everything is self-explanatory.

image

After choosing three settings, we can do a “test connection” and see whether this client is able to connect to SQL Server or not. In case of problem, correct error from native client would be thrown.

This is one of the test which every troubleshooter should know.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in Troubleshooting | Tagged: , , , | 2 Comments »

    Troubleshooting: Cannot execute as the database principal and Property Owner is not available for Database

    Posted by blakhani on March 4, 2014


    No one likes errors in the world. As a part of my work with Microsoft SQL Support Services, I get to see various errors, find cause and provide solution to get rid of them. Today someone reported below error to me.

    Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    This error message was caused because SQL Server was not able to find context information for the security logon we were attempting to impersonate. In this case it was “dbo”. Going with common sense, I went ahead to check the owner of the database, which is generally visible in database > right click > properties. As soon as I attempted it, I was welcomed with next error message.

    image

    TITLE: Microsoft SQL Server Management Studio
    ——————————
    Cannot show requested dialog.
    ——————————
    ADDITIONAL INFORMATION:
    Cannot show requested dialog. (SqlMgmt)
    ——————————
    Property Owner is not available for Database ‘[SQLServerHelp]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1746.0+((KJ_RTM_QFE-CU).101005-1216+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Owner&LinkId=20476
    ——————————
    BUTTONS:
    OK
    —————————— 
     

    Okay. This is an interesting message. Notice the exception event source is “PropertyCannotBeRetrievedExceptionText” and exception event id “Owner”. The message means that on SMO, the value of “owner” property is not available. This means something is not right with owner of the database?

    Next step was to find out who the current owner (without management studio). There are multiple ways to do it.

    • sp_helpdb

    image

    • sp_helpdb ‘DatabaseName’

    image

    • T-SQL which is used by sp_helpdb 
    select name, isnull(suser_sname(sid),'~~UNKNOWN~~'), 
convert(nvarchar(11), crdate),
dbid, cmptlevel from master.dbo.sysdatabases

     

    image

    As we can see above, “~~UNKNOWN~~” is not something which is not expected and that’s the reason SSMS was showing exception to get owner property.

    How to fix it?

    Of course, change the owner of the database so that value of SID column holds valid SID. Since we are not able to use UI, we have to use sp_changedbowner (change db owner with no spaces). This has be to run in the context of the database which has problem, as shown below. I have given sa as an example for my database.

    image

    Root Cause/Repro of the issue

    If a database is owned by a login, we would not be able to drop the login. The possible situation which I can think of are:

    • Database owned by windows login which was part of any Windows Group. And now, windows account was deleted from active directory. 
    • Database was restored and domain controller was not available to resolve the SID to a validate login.

    In my repro, I was able to get into same situation by step 1.

    Important: I was NOT able to reproduce the “property owner” error in SQL Server 2014 Management Studio. It doesn’t throw exception of owner property and it can be blank in the UI.

    image

    This also means that owner can be changed via management studio by going to Properties  > Files tab.

    image

    Hope you have learned something new.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in Error, SQL Server, SSMS | Tagged: , , , , , | Leave a Comment »

    Information : Why we can’t use system database files from “Template” folder rather than rebuilding system databases

    Posted by blakhani on February 27, 2014


    In my recent conversation on SQL Bangalore User Group page, I had interesting discussion about rebuilding system database in SQL Server 2008. One of the community member mentioned that we can take the master database files from below location

    C:\Program Files\Microsoft SQL Server\<InstanceName>\MSSQL\Binn\Templates

    and keep them to the current location of master.

    I have explained that why above will not work but there is so much a write about it. So, thought of writing a blog post (for better search because Facebook comments are not shown as result by search engines)

    Let’s first understand why we keep those file in that location. In earlier version of SQL (2000 and 2005) if there is a need to rebuild system databases, we need to get the installation media (DVD or network share). In SQL 2008 onwards, we don’t need DVD or media to rebuild system databases. During installation of SQL, setup does the caching of setup.exe (and other files needed for setup) and also the MDF and LDF file in Template folder. In this directory you will find master, model, and msdb database and log files that were copied from your installation source as part of setup. The path of setup.exe for SQL 2012 is “C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012”. You can easily locate similar folder for SQL 2008 or R2 as well.

    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\Templates

    So the logic in rebuilding the system databases is to get these files and copy them into your DATA directory to obtain your new system databases. Now, you may ask, what would happen if I just copy those files from Templates to data folder? I’ll explain then in a bit. Setup does few more things other than copy paste.

    image

    If we rebuild the system databases, setup would move the files from above location to the proper location and (this is important) it modifies the path of other databases which is stored in master database. When SQL was packaged, we didn’t know you are going to use F drive (or any other drive) to keep system databases so the path is from a machine where SQL product was packaged.

    Let’s see what would happen if I just move the files and start SQL Server. Well, you might have guessed it, we won’t be able to start SQL. Notice the path and filename which I highlighted below.

    2013-12-06 16:00:51.61 spid9s      Starting up database ‘mssqlsystemresource’.
    2013-12-06 16:00:51.62 spid5s      Error: 17204, Severity: 16, State: 1.
    2013-12-06 16:00:51.62 spid5s      FCB::Open failed: Could not open file e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBData.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
    2013-12-06 16:00:51.62 spid5s      Error: 5120, Severity: 16, State: 101.
    2013-12-06 16:00:51.62 spid5s      Unable to open the physical file "e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBData.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
    2013-12-06 16:00:51.62 spid5s      Error: 17207, Severity: 16, State: 1.
    2013-12-06 16:00:51.62 spid5s      FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf’. Diagnose and correct the operating system error, and retry the operation.
    2013-12-06 16:00:51.62 spid5s      File activation failure. The physical file name "e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf" may be incorrect.
    2013-12-06 16:00:51.67 spid9s      The resource database build version is 11.00.3000. This is an informational message only. No user action is required.
    2013-12-06 16:00:51.98 spid9s      Starting up database ‘model’.
    2013-12-06 16:00:51.98 spid9s      Error: 17204, Severity: 16, State: 1.
    2013-12-06 16:00:51.98 spid9s      FCB::Open failed: Could not open file e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\model.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
    2013-12-06 16:00:51.98 spid9s      Error: 5120, Severity: 16, State: 101.
    2013-12-06 16:00:51.98 spid9s      Unable to open the physical file "e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\model.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
    2013-12-06 16:00:51.99 spid9s      Error: 17207, Severity: 16, State: 1.
    2013-12-06 16:00:51.99 spid9s      FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\modellog.ldf’. Diagnose and correct the operating system error, and retry the operation.
    2013-12-06 16:00:51.99 spid9s      File activation failure. The physical file name "e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\modellog.ldf" may be incorrect.
    2013-12-06 16:00:52.02 spid9s      Error: 945, Severity: 14, State: 2.
    2013-12-06 16:00:52.02 spid9s      Database ‘model’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

    This is one of the error which you might also see when SQL installation is not complete and then SQL Service not getting started. People have posted on various forums about this problem. The root cause of the issue was that SQL Setup couldn’t alter the location for other system databases and hence looking for some weird path. If its SQL 2008 you might see path as e:\sql10_main_t\sql\mkmastr\databases\objfre\i386\

    Here is the information taken from SQL Installation logs which confirms our theory. They are from my own machine

    • Files getting copied

    (01) 2013-07-02 10:24:27 SQLEngine: : Installing system database files
    (01) 2013-07-02 10:24:27 Slp: Sco: File ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf’ does not exist
    (01) 2013-07-02 10:24:27 SQLEngine: : Copying database file ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates\master.mdf’ to ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf’
    (01) 2013-07-02 10:24:27 Slp: Sco: Attempting to copy file ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates\master.mdf’ to file ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf’
    (01) 2013-07-02 10:24:27 Slp: Sco: Attempting to get security descriptor for file ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates\master.mdf’
    (01) 2013-07-02 10:24:27 Slp: Sco: Returning security descriptor O:SYG:SYD:AI(A;ID;FA;;;SY)(A;ID;FA;;;BA)(A;ID;0x1200a9;;;BU)(A;ID;0x1200a9;;;S-1-5-80-3880718306-3832830129-1677859214-2598158968-1052248003)

    • Same operation done for mastlog.ldf, model.mdf, modellog.ldf, MSDBData.mdf and MSDBLog.ldf as well.
    • Then SQL is started with various trace flags, parameters as shown below.

    (01) 2013-07-02 10:28:08 SQLEngine: –SqlServerServiceSCM: Starting SQL via SCM (-mSqlSetup -Q -qSQL_Latin1_General_CP1_CI_AS -T4022 -T4010 -T3659 -T3610 -T8015)…

    • A connection is made and the various scripts would be executed to modify the information in files picked from template folder.

    (01) 2013-07-02 10:28:19 SQLEngine: –SqlDatabaseServiceConfig: Connection String: Data Source=\\.\pipe\SQLLocal\MSSQLSERVER;Initial Catalog=master;Integrated Security=True;Pooling=False;Connect Timeout=300;Network Library=dbnmpntw;Application Name=SqlSetup
    (01) 2013-07-02 10:28:19 SQLEngine: : Checking Engine checkpoint ‘ServiceConfigConnect’
    (01) 2013-07-02 10:28:19 SQLEngine: –SqlDatabaseServiceConfig: Connecting to SQL….
    (01) 2013-07-02 10:28:19 Slp: Sco: Attempting to connect script
    (01) 2013-07-02 10:28:19 Slp: Connection string: Data Source=\\.\pipe\SQLLocal\MSSQLSERVER;Initial Catalog=master;Integrated Security=True;Pooling=False;Connect Timeout=300;Network Library=dbnmpntw;Application Name=SqlSetup
    (01) 2013-07-02 10:28:19 Slp: Connected successfully…
    (01) 2013-07-02 10:28:19 SQLEngine: –SqlDatabaseServiceConfig: Running queries SQL….
    (01) 2013-07-02 10:28:19 SQLEngine: : Install script parameters:
    (01) 2013-07-02 10:28:19 SQLEngine: :  Instance Name: MSSQLSERVER
    (01) 2013-07-02 10:28:19 SQLEngine: :  Sql Collation: SQL_Latin1_General_CP1_CI_AS
    (01) 2013-07-02 10:28:19 SQLEngine: :  System Data Dir: Microsoft.SqlServer.Configuration.Sco.SqlDirectory
    (01) 2013-07-02 10:28:19 SQLEngine: :  Tempdb Dir: Microsoft.SqlServer.Configuration.Sco.SqlDirectory
    (01) 2013-07-02 10:28:19 SQLEngine: :  Tempdb Log Dir: Microsoft.SqlServer.Configuration.Sco.SqlDirectory
    …

     

    To conclude, you should not use system database files from “Templates” folder as it is by using copy paste method. You must use /ACTION=REBUILDDATABASE

    Hope this would explain a little about usage of template folder.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in SQL Server User Group, SQLBangUG | Tagged: , , , , , | 8 Comments »

    « Previous Entries
    Next Entries »