Solved

MSSQL 2008r2 Express User Instance DB creation and access

Posted on 2011-09-04
3
340 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

773 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