Solved

MSSQL 2008r2 Express User Instance DB creation and access

Posted on 2011-09-04
3
334 Views
Last Modified: 2012-08-13
My app has been working fine with using MSSQL 2008r2  through an IP address.  However, I'm getting to a point where I want to distribute the application and from what I'm reading using a user instance will help me get around having the end user manually set TCPIP settings in MSSQL2008r2 express.

I'm able to get the DB to create using:
            szQuery = @"CREATE DATABASE MyDB " +
                      "ON PRIMARY (NAME=MyDB_Data, " +
                      "FILENAME = '" + szDBPath + "')";

            try
            {
                using (SqlConnection aConnection = new
                    SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=master;Integrated Security=SSPI;User Instance=true;"))// ;AttachDbFilename=" + szDBPath )) 
                {
                    aConnection.Open();
                    using (var dbCommand = aConnection.CreateCommand())
                    {
                        dbCommand.CommandText = szQuery;
                        dbCommand.ExecuteNonQuery();
                    }
                }
            }

Open in new window


But then when I try to access the DB I get a login failed for the same user that created it:

            try
            {
                using (SqlConnection aConnection = new 
                    SqlConnection(@"server=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=MyDB;User Instance=true;")
                {
                    aConnection.Open();
                    using (SqlCommand dbCommand = new SqlCommand(szQuery, aConnection))
                    {
                        dbCommand.ExecuteNonQuery();
                    }
                }
            }

Open in new window


Failes with: "Cannot open database "MyDB" requested by the login. The login failed.
Login failed for user 'MyAD\sj'."

I don't just want to fix the issue; I want to understand what's going on...

Once this happens the files located in user\sj\...\sqlexpress\*.* are held open and even shutting down the sqlexpress service doesn't seem to help.   The only way I can remove the DB and retry is to restart the computer then delete the files.

Can I access the DB from studio express?  The DB doesn't show when I open it.

Anyone have any insight? Thanks.
0
Comment
Question by:sej69
  • 2
3 Comments
 
LVL 15

Expert Comment

by:dave4dl
ID: 36480741
I see a lot of things that you dont do in your database creation command that are normally done (like creating a log for the database).  From the sounds of it, the database creation is not complete so it just hangs waiting for the rest of the database creation commands it needs.  Attached is an example creation script.
USE [master]
GO

/****** Object:  Database [MyDB]    Script Date: 09/04/2011 09:23:45 ******/
CREATE DATABASE [MyDB] ON  PRIMARY 
( NAME = N'MyDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLSERVER2008\MSSQL\DATA\MyDB.mdf' , SIZE = 8741888KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MyDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLSERVER2008\MSSQL\DATA\MyDB_log.ldf' , SIZE = 9027840KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [MyDB] SET COMPATIBILITY_LEVEL = 90
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MyDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [MyDB] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [MyDB] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [MyDB] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [MyDB] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [MyDB] SET ARITHABORT OFF 
GO

ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [MyDB] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [MyDB] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [MyDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [MyDB] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [MyDB] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [MyDB] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [MyDB] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [MyDB] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [MyDB] SET  DISABLE_BROKER 
GO

ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [MyDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [MyDB] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [MyDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [MyDB] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [MyDB] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [MyDB] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [MyDB] SET  READ_WRITE 
GO

ALTER DATABASE [MyDB] SET RECOVERY FULL 
GO

ALTER DATABASE [MyDB] SET  MULTI_USER 
GO

ALTER DATABASE [MyDB] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [MyDB] SET DB_CHAINING OFF 
GO

Open in new window

0
 

Accepted Solution

by:
sej69 earned 0 total points
ID: 37204233
Can't use integrated_security in a user instance...  Problem was resolved.
0
 

Author Closing Comment

by:sej69
ID: 37230545
I solved the issue.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now