Solved

MSSQL 2008r2 Express User Instance DB creation and access

Posted on 2011-09-04
3
345 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

730 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