Creating SQL Server Databases With No Security Restrictions

First, let me apologize for crossposting and the lengthy post, but this
question involved both MSDE and (I believe) security settings.
We use Visual Studio .NET 2003 with MSDE in classrooms and labs and have
had no security issues once things were configured properly for student
access. The sample databases install and work correctly for any authorized
users who might be logged on to a given PC.
We are using a text that supplies .mdf files as part of the support
materials for the book. We are able to copy the .mdf files to the
appropriate directory (..\Microsoft SQL Server\MSSQL$InstanceName\Data),
then use Server Explorer in VS .NET to create a database link to the file
(Connect To Database button on the Server Explorer toolbar). Once VS .NET is
restarted, the database appears in the list of databases under the SQL
Server instance and is fully accessible.
I now want to create additional SQL Server databases for students to use
with development projects, etc. I have access to the SQL Server GUI
management tools (from our MSDNAA subscription) and they are installed and
working with the MSDE instance on my office PC. When I create a
database/tables/etc. on my instance, then attempt to copy the .mdf to
another PC to use the setup technique described above, a window pops up
wanting me to select a security option and then refuses me access to the new
database regardless of the option (NT Security or database
username/password) I try to choose.
After all that, what I'm seeking is the way to create a database to export
to any PC, with any student or faculty member logged on so that they can
import the .mdf file and connect to it using Server Explorer *without*
encountering security issues. So, when I'm creating a database, what
settings do I apply so that it doesn't care who is interacting with it?
Thanks in advance for reading and any insights!
hi Peter,
Peter van der Goes wrote:
> First, let me apologize for crossposting and the lengthy post, but
> this question involved both MSDE and (I believe) security settings.
> We use Visual Studio .NET 2003 with MSDE in classrooms and labs and
> have had no security issues once things were configured properly for
> student access. The sample databases install and work correctly for
> any authorized users who might be logged on to a given PC.
> We are using a text that supplies .mdf files as part of the support
> materials for the book. We are able to copy the .mdf files to the
> appropriate directory (..\Microsoft SQL
> Server\MSSQL$InstanceName\Data), then use Server Explorer in VS .NET
> to create a database link to the file (Connect To Database button on
> the Server Explorer toolbar). Once VS .NET is restarted, the database
> appears in the list of databases under the SQL Server instance and is
> fully accessible.
> I now want to create additional SQL Server databases for students to
> use with development projects, etc. I have access to the SQL Server
> GUI management tools (from our MSDNAA subscription) and they are
> installed and working with the MSDE instance on my office PC. When I
> create a database/tables/etc. on my instance, then attempt to copy
> the .mdf to another PC to use the setup technique described above, a
> window pops up wanting me to select a security option and then
> refuses me access to the new database regardless of the option (NT
> Security or database username/password) I try to choose.
> After all that, what I'm seeking is the way to create a database to
> export to any PC, with any student or faculty member logged on so
> that they can import the .mdf file and connect to it using Server
> Explorer *without* encountering security issues. So, when I'm
> creating a database, what settings do I apply so that it doesn't care
> who is interacting with it?
> Thanks in advance for reading and any insights!
in order to attach an existing db file, as long as create a brand new
database, the logged user must be member of the sysadmin and/or dbcreator
server role...
in order to connect to an MSDE instance you have to provide a login
information, both for Windows NT (trusted) authenticated or SQL Server
authenticated connections, as you provide this kind of information in the
Data Link dialog to (Connect To Database button on the Server Explorer
toolbar)... so there's no difference in the behaviour you are
experimenting..
what kind of error do you get when accessing the attached database?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:36sc8vF556sgkU1@.individual.net...
> hi Peter,
> in order to attach an existing db file, as long as create a brand new
> database, the logged user must be member of the sysadmin and/or dbcreator
> server role...
> in order to connect to an MSDE instance you have to provide a login
> information, both for Windows NT (trusted) authenticated or SQL Server
> authenticated connections, as you provide this kind of information in the
> Data Link dialog to (Connect To Database button on the Server Explorer
> toolbar)... so there's no difference in the behaviour you are
> experimenting..
> what kind of error do you get when accessing the attached database?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
Thanks for responding, Andrea.
The issue is not permissions assigned to a given user (all users must log on
to any campus PC, thus providing username/password information), but the
difference between a .mdf file I create vs. those supplied by the publisher
of the textbook.
When we work with the .mdf files supplied by the publisher, there is no
issue with requested authentication information. Any user who can log onto a
campus PC can copy the .mdf file to the correct location on that PC, follow
the procedures I outlined in my original post using Visual Studio .NET 2003
Server Explorer and "create" an accessible database using the file in
question, then access it and its objects in Server Explorer at will. Some
setting in that .mdf file is allowing this behavior for any user. This
database can be "opened" by anybody who logs onto that PC and uses Server
Explorer.
When I create a database (.mdf) file using SQL Server Enterprise Manager and
try to "create" a database from it on a different PC, the process appears to
work. When I subsequently attempt to open that database in Server Explorer
(by clicking on the + next to the newly-created database name), I get a
dialog box insisting that I supply either NT Security information or, select
SQL Server access and provide a username/password for the database. Any
combination of username/password information I supply at that stage is
rejected by the dialog. When I open a database supplied by the publisher, no
such dialog appears and I have full access. My goal is to create databases
which behave like those supplied by the publisher.
As the security issue does not occur when using the .mdf files provided by
the publisher, I can only assume that these files were from databases set up
as accessible to all. As I'm not smart on SQL Server administration
(everything I "know" comes from SQL Server 2000 Programming, Step by Step
from MS Press), I'm hoping that somebody will guide me to the correct
settings for a SQL Server database so it's openly accessible to any user who
logs onto a PC where the database resides without triggering any
authentication dialogs. For what it's worth, I can replicate all the
behaviors above on my home PC which just uses a simple default logon.
I can send you (or, anybody else) one of the publisher's .mdf files if it
would help identify the settings in question.
|||On Tue, 8 Feb 2005 12:50:40 -0600, Peter van der Goes wrote:

> When I create a database (.mdf) file using SQL Server Enterprise Manager and
> try to "create" a database from it on a different PC, the process appears to
> work. When I subsequently attempt to open that database in Server Explorer
> (by clicking on the + next to the newly-created database name), I get a
> dialog box insisting that I supply either NT Security information or, select
> SQL Server access and provide a username/password for the database. Any
> combination of username/password information I supply at that stage is
> rejected by the dialog. When I open a database supplied by the publisher, no
> such dialog appears and I have full access. My goal is to create databases
> which behave like those supplied by the publisher.
Perhaps this would do the trick:
EXECUTE sp_grantdbaccess guest
EXECUTE sp_addrolemember 'db_owner',guest
Sorry, but I couldn't find an equivalent to this in Enterprise Manager, so
execute it from Query Analyzer.
|||"Ross Presser" <rpresser@.imtek.com> wrote in message
news:uauu8880rpzp$.dlg@.rpresser.invalid...
> On Tue, 8 Feb 2005 12:50:40 -0600, Peter van der Goes wrote:
> Perhaps this would do the trick:
> EXECUTE sp_grantdbaccess guest
> EXECUTE sp_addrolemember 'db_owner',guest
> Sorry, but I couldn't find an equivalent to this in Enterprise Manager, so
> execute it from Query Analyzer.
Unfortunately, the commands didn't alter the situation. However, through
additional experimentation, I've discovered the following:
The database itself is OK, as I can access it in Enterprise Manager. In
Server Explorer, the database appears, but its State property says
"Disconnected" and I get the SQL Server authentication dialog when Server
Explorer attempts to connect. I've checked the database file's network
security properties, and they appear to be set the same as those for the
database files (from the book) that I am able to access in Server Explorer.
Public has full access. I'm beginning to think I'm dealing with a NTFS file
or folder access issue, as the same database is fully accessible through
Server Explorer on a PC with a FAT32 partition?!?