sp_configure `user instances enabled`

download report

Transcript sp_configure `user instances enabled`

Installing sylib
By,
Tolga Ciftci
Main Aims
• Installing the website with the setup file
– With SQL Express Server and user
instancing.
Setup files
– 2 setup files for 2 setup types.
– Precompiled setup
•
•
•
•
The sources are precompiled.
No source files for web pages.
In folder “sylibPrecompSetup”
Advantages
– Making It Harder for Prying Eyes
• Disadvantages
– Harder to update the site
– Preparing an updateable precompiled web site is
possible. But this is not updateable.
Setup files
• Setup
– Not precompiled
– In folder “sylibSetup”
– Advantages
• Easier to prepare
• Can change files in place with notepad.
– Disadvantages
• Less secure
Setup files
• They are installed the same way
About User Instances and
SQLExpress
• Gives “database admin rights” to nonadmin processes/users for database files
that they can access.
– Asp.Net web application is not admin; default
user name “Network Service” in sy machines.
• An instance of SQL Express server with
client’s rights and name created.
– See http://msdn2.microsoft.com/enus/library/bb264564.aspx for a discussion
About User Instances and
SQLExpress
• The web site expects an SQL Express
server instance with “user instance”
option enabled and with instance name
SQLExpress.
– Default installation of SQL Express has this
instance name.
– Can check by trying to connect with “SQL
Server Management Studio”.
• Express edition of management studio will also
work.
Connecting to SQL Express
• Open Management
studio
– Enter “.\SQLExpress”
in server name.
• “.\” server addres.
Means localhost
• SQLExpress: Server
instance name
– Authentication is
“Windows
Authentication”
• Press connect
Management Studio
Execute
button
SQL query editor
connected to
(local)\SQLExpress
Object
Explorer
Results
window
Checking “User Instances” option
• In Management
Studio object explorer
window, right click on
the server name and
choose new query.
• In the SQL editor
window write
“sp_configure 'user
instances enabled‘”
• Press execute button
Checking “User Instances” option
• If the results window
shows a row like the one
on the left, “user
instances” is enabled.
• In the other case execute
– sp_configure 'user
instances enabled','1‘;
RECONFIGURE;
GO
• Change “1” with “0” to
disable
Checking “User Instances” option
• For Summer 2007 SQLServer express
seems to be installed correctly on sy
machines.
• You don’t need to check this for summer
2007
Installing the site
• Double click on the
setup exe program
• SylibSetup dialog
appears.
• Click “Next >” button
Installing the site
• On the next windows
• Choose site
• In the virtual directory
write a meaningful name
that wont clash with other
people using the same
machine
– For example sylibsy01a
– I used sylibteam2007 in
this example.
• Click “Next >” button
Installing the site
• Wait for completion of
setup
• Click on the “Close”
button.
Checking installation directory
• Go to “c:\Inetpub\wwwroot\<the virtual
directory name from setup>
– In our example this will be
“c:\Inetpub\wwwroot\sylibteam2007”
• Now we can check whether site works or
not
– Browse to “http://localhost/<the virtual
directory name from setup>”
Possible errors
• In some sy machines the .Net 2.0 is not registered in the
ISS.
– This gives an error that says “Parser error message:
Unrecognized attribute xmlns” in web browser.
– See http://msdn2.microsoft.com/en-us/asp.net/aa336670.aspx
for an explanation and solution
– Basically execute aspnet_regiis executable from: %windows root
directory%\Microsoft.NET\Framework\%version of the .NET
Framework%\aspnet_regiis -r
• In sy machines this is
“C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\
aspnet_regiis -r”
– The “-r” option associates all web applications with ASP.Net 2.0.
if this is not OK for your setup use “-ir” option. You can look at
http://weblogs.asp.net/owscott/archive/2006/01/26/436607.aspx
Changing permission of database
files
• Since SQL Server express uses user
instancing while attaching the files, the
process attaching the database files need
to have read-write access to the files.
– The process attaching DB files is the ISS
application pool which uses identity “Network
Service”
– Giving “Network Service” read-write access to
these files solves the “read-only database” file
problem.
Changing Permissions of DB files
• The database files are
under “App_Data”
subfolder of the
application folder.
– The file extensions are;
“.mdf” and “.ldf”.
• Select all of these files.
– Right click and choose
properties
– In the upcoming properties
dialog go to security tab
Changing Permissions of DB
files(Continued)
1
2
b
a
1. Press Add button under
“Groups or User names”
list
2. “Select users or groups”
dialog box comes up.
a. In the name field, write
“network service”
b. For the location field,
choose the computer you
are connected to.
c. Click OK.
Changing Permissions of DB files
• Give read,write and
execute rights to
“Network Service”
– Execute right may not
be needed.
• Only “Administrators”
and “Network
Service” in the users
list seems enough.
Extra Information
• In vista the installation program may not
work right out of the box. This maybe due
to the fact that VBScript is not registered.
– See
http://blogs.msdn.com/fzandona/archive/2007/
03/30/wm-6-sdk-install-issue-common7-ideprojecttemplates.aspx for a similar issue and
possible solution. We do not recommend or
reject this solution.
Extra Information
• At times you may want to move/copy/delete the
database file and not able to do it due to the fact
they are used by another process (probably SQL
Server Express)
– Waiting for some amount of time or restarting the
machine may work.
– Process explorer from Microsoft maybe helpful:
http://www.microsoft.com/technet/sysinternals/utilities/
ProcessExplorer.mspx
• Note that closing file handles using this tool may cause some
data corruption or damage the DB files.
References
• The references shown in this presentation
are obtained by googling and are not
necessarily from sources we know.