MSDE Secury

Can I distribute a MSDE 1.0 database(SQL Server 7) so that the user cannot
see its contents even if the user has SQL server installed on his machine?
I use visual basic 6.0 and I need to distribute data that cannot be seen(nor
opened) by the user, except from the VB 6 application.
Rick
hi Rick,
Rick wrote:
> Can I distribute a MSDE 1.0 database(SQL Server 7) so that the user
> cannot see its contents even if the user has SQL server installed on
> his machine?
> I use visual basic 6.0 and I need to distribute data that cannot be
> seen(nor opened) by the user, except from the VB 6 application.
no, you can't... every SQL Server administrator (member of server's
sysadmins) will be able to access your database...
for further info please have a look at http://tinyurl.com/9rzrd
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||If it's sensitive text you want to protect from prying eyes, you could
introduce an encryption layer to your program - encrypt text before your save
it on the DB and decrypt it after selecting.
Jamie Clancy
Ulysses Systems, London
"Rick" wrote:

> Can I distribute a MSDE 1.0 database(SQL Server 7) so that the user cannot
> see its contents even if the user has SQL server installed on his machine?
> I use visual basic 6.0 and I need to distribute data that cannot be seen(nor
> opened) by the user, except from the VB 6 application.
>
> --
> Rick

msde security - windows mode?

Hi,
I want to be able to use SQL authentication on my local MSDE 2000 , I've
added a user using sp_addlogin, and try to connect using it, however it
always gives me 'not associated with a trusted SQL server connection'. Is
there any way I can force it to go to SQL mode without reinstalling it?
Thanks> I want to be able to use SQL authentication on my local MSDE 2000 , I've
> added a user using sp_addlogin, and try to connect using it, however it
> always gives me 'not associated with a trusted SQL server connection'. Is
> there any way I can force it to go to SQL mode without reinstalling it?
Yes - check the article at http://support.microsoft.com/kb/285097/EN-US/.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||> I want to be able to use SQL authentication on my local MSDE 2000 , I've
> added a user using sp_addlogin, and try to connect using it, however it
> always gives me 'not associated with a trusted SQL server connection'. Is
> there any way I can force it to go to SQL mode without reinstalling it?
Look at my answer in .security group.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com

msde restore problem

Hi guys - hope you can help me.
We had a support request from a user today who wanted to know why some of
her data was missing. Upon investigation, it turns out that due to various
things... we'd had to image back the server. Now, we took a copy of the
directory with all the database files in before we did that. So we tried to
restore that today, and it said it had worked, but the data is still missing
!
Question.. if we restored the FILES of the database over themselves, as the
database was running, would we need to restart the sql server agent thingy?
Or should we do that first, restore the files, and then restart MSDE?
Would it make any difference? Or to be more precise, would overwriting the
files (its an open file restore, but it said it worked?) require msde to be
restarted?!
Cheers
Danmusosdev wrote:
> Hi guys - hope you can help me.
> We had a support request from a user today who wanted to know why some of
> her data was missing. Upon investigation, it turns out that due to various
> things... we'd had to image back the server. Now, we took a copy of the
> directory with all the database files in before we did that. So we tried t
o
> restore that today, and it said it had worked, but the data is still missi
ng!
> Question.. if we restored the FILES of the database over themselves, as th
e
> database was running, would we need to restart the sql server agent thingy
?
> Or should we do that first, restore the files, and then restart MSDE?
> Would it make any difference? Or to be more precise, would overwriting the
> files (its an open file restore, but it said it worked?) require msde to b
e
> restarted?!
> Cheers
>
> Dan
Oh for crying out loud, did you actually use the word "thingy"? Sure
sign of a competent tech.
You should have made a proper SQL backup, instead of trying to copy the
raw database files. Those files were in use, unless you stopped the
MSSQLSERVER service. You've most likely lost data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi, Dan,
Thanks for your post!
And Thanks Tracy for your kind participation and good suggestions.
Dan, my understanding of this issue is:
The database lost data that might be caused by many reasons. You had a copy
of that database data directory and decided to restore the files of the
database over themselves. You wanted to know whether you need restart the
SQL Server Agent at first and whether the MSDE need to be restarted.
If I have misunderstood, please let me know.
Do you mean that your backup files are raw files (.mdf,.ldf) of the
database and not real backup files (.bak) ?
If they are .bak files, you can directly use the RESTORE statement to
restore the database.
Otherwise, you can take the following steps to replace the files without
stop the MSDE:
1) Backup your database by "BACKUP DATABASE database_name TO
DISK='D:\temp_folder\test.bak' "
2) Run the statement "sp_detach_db [database_name]"
3) Remove the database files in the local directory.
4) Copy the backup files to the directory
5) Use the statement sp_attach_db like this:
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
You may refer to:
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
Otherwise, are you sure that the backup files have no data loss? As Tracy
said, for time difference, they still possibly have lost data.
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Dan,
Just checking in to see if the suggestions were helpful. Please let us know
if you would like further assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

msde restore problem

Hi guys - hope you can help me.
We had a support request from a user today who wanted to know why some of
her data was missing. Upon investigation, it turns out that due to various
things... we'd had to image back the server. Now, we took a copy of the
directory with all the database files in before we did that. So we tried to
restore that today, and it said it had worked, but the data is still missing!
Question.. if we restored the FILES of the database over themselves, as the
database was running, would we need to restart the sql server agent thingy?
Or should we do that first, restore the files, and then restart MSDE?
Would it make any difference? Or to be more precise, would overwriting the
files (its an open file restore, but it said it worked?) require msde to be
restarted?!
Cheers
Danmusosdev wrote:
> Hi guys - hope you can help me.
> We had a support request from a user today who wanted to know why some of
> her data was missing. Upon investigation, it turns out that due to various
> things... we'd had to image back the server. Now, we took a copy of the
> directory with all the database files in before we did that. So we tried to
> restore that today, and it said it had worked, but the data is still missing!
> Question.. if we restored the FILES of the database over themselves, as the
> database was running, would we need to restart the sql server agent thingy?
> Or should we do that first, restore the files, and then restart MSDE?
> Would it make any difference? Or to be more precise, would overwriting the
> files (its an open file restore, but it said it worked?) require msde to be
> restarted?!
> Cheers
>
> Dan
Oh for crying out loud, did you actually use the word "thingy"? Sure
sign of a competent tech.
You should have made a proper SQL backup, instead of trying to copy the
raw database files. Those files were in use, unless you stopped the
MSSQLSERVER service. You've most likely lost data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi, Dan,
Thanks for your post!
And Thanks Tracy for your kind participation and good suggestions.
Dan, my understanding of this issue is:
The database lost data that might be caused by many reasons. You had a copy
of that database data directory and decided to restore the files of the
database over themselves. You wanted to know whether you need restart the
SQL Server Agent at first and whether the MSDE need to be restarted.
If I have misunderstood, please let me know.
Do you mean that your backup files are raw files (.mdf,.ldf) of the
database and not real backup files (.bak) ?
If they are .bak files, you can directly use the RESTORE statement to
restore the database.
Otherwise, you can take the following steps to replace the files without
stop the MSDE:
1) Backup your database by "BACKUP DATABASE database_name TO
DISK='D:\temp_folder\test.bak' "
2) Run the statement "sp_detach_db [database_name]"
3) Remove the database files in the local directory.
4) Copy the backup files to the directory
5) Use the statement sp_attach_db like this:
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
You may refer to:
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
Otherwise, are you sure that the backup files have no data loss? As Tracy
said, for time difference, they still possibly have lost data.
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Dan,
Just checking in to see if the suggestions were helpful. Please let us know
if you would like further assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.sql

MSDE or SQL-SERVER ?? help

Hello can someone help me out, i need to know if i buy the 5 User Cal license of MS-SQL server, does that mean that only 5 people on the network or through the web can access the database? Is that true?
How can i use the SQL Server in the following way:
2-4 users running on a local area network
10-15 concurrent users online
Database size will grow each year by 800-1000mb, i know the MSDE limit is 2Gig,
How can i get a solution to the above problem? what is my best bet to utilize SQL-Server? MSDE Or some other type of sql product?
MSDE has been shown to support far more than 10-15 users. It all depends on
how well the application is written and how your code manages the connection
and the server resource. SQL Express which is due out in 2005 ups the limit
to 4GB. However, with appropriate archiving, you can keep within the limits.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Artmic" <Artmic@.discussions.microsoft.com> wrote in message
news:703D7809-DB32-4557-825D-0C7F1F689BEC@.microsoft.com...
> Hello can someone help me out, i need to know if i buy the 5 User Cal
license of MS-SQL server, does that mean that only 5 people on the network
or through the web can access the database? Is that true?
> How can i use the SQL Server in the following way:
> 2-4 users running on a local area network
> 10-15 concurrent users online
> Database size will grow each year by 800-1000mb, i know the MSDE limit is
2Gig,
> How can i get a solution to the above problem? what is my best bet to
utilize SQL-Server? MSDE Or some other type of sql product?
>
|||Ok thanks, i guess i will go ahead with the MSDE and switch to SQL Express when it is available... it is just not economic for a small site to buy a multi-thousand dollar processor license for Online volume that is laughable IMO.
"William (Bill) Vaughn" wrote:

> MSDE has been shown to support far more than 10-15 users. It all depends on
> how well the application is written and how your code manages the connection
> and the server resource. SQL Express which is due out in 2005 ups the limit
> to 4GB. However, with appropriate archiving, you can keep within the limits.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> "Artmic" <Artmic@.discussions.microsoft.com> wrote in message
> news:703D7809-DB32-4557-825D-0C7F1F689BEC@.microsoft.com...
> license of MS-SQL server, does that mean that only 5 people on the network
> or through the web can access the database? Is that true?
> 2Gig,
> utilize SQL-Server? MSDE Or some other type of sql product?
>
>

MSDE on XP running, but not recognized in ODBC

I installed an MSDE instance for a user running XP with an
admin Windows account. The MSDE Server icon appears in
the notification area, but without the green arrow. Task
manager shows that it is running. Trying to start it up
manually doesn't do anything. When I go to add the Data
Source in ODBC, System DSN (MDAC 2.8 is installed), when
we go to pick the MSDE Server, it doesn't appear in the
drop down when picking a server. Any thoughts or ideas
would be great. Thanks, JRaub.
hi JRaub,
JRaub wrote:
> I installed an MSDE instance for a user running XP with an
> admin Windows account. The MSDE Server icon appears in
> the notification area, but without the green arrow. Task
> manager shows that it is running. Trying to start it up
> manually doesn't do anything. When I go to add the Data
> Source in ODBC, System DSN (MDAC 2.8 is installed), when
> we go to pick the MSDE Server, it doesn't appear in the
> drop down when picking a server. Any thoughts or ideas
> would be great. Thanks, JRaub.
please have a look at
http://support.microsoft.com/default...b;EN-US;814132
if you want to modify the networkprotocols setting, please run the Server
Network Utility (svrnetcn.exe) and enable the desired protocol
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. I'll give this a shot. Nothing else has worked.[vbcol=seagreen]
>--Original Message--
>hi JRaub,
>JRaub wrote:
an[vbcol=seagreen]
Task
>please have a look at
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;814132
>if you want to modify the networkprotocols setting,
please run the Server
>Network Utility (svrnetcn.exe) and enable the desired
protocol
>--
>Andrea Montanari (Microsoft MVP - SQL Server)
>http://www.asql.biz/DbaMgr.shtm
http://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
>
>.
>

MSDE newbie

Hi guys,
I am a new user in msde and I'm wondering what do I need to get it to work. Currently I've already got the engine installed on my pc. So where do I go from here? Thanks in advance.

Ok, I managed to find out that MSDE can be utilized from the web matrix. I went through the walkthrough provided on the web matrix homepage and was able to create a database of my own. However, now I've got another problem. When I run the aspx page in the web matrix server, everything works. However, when I run it using the IIS, the following error is thrown:

Server Error in '/chapter9' Application.

---

Login failed for user 'silvertype\ASPNET'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Login failed for user 'silvertype\ASPNET'.
Source Error:

Line 7: Line 8: conPubs = New SqlConnection( "server='(local)'; trusted_connection=true; database='pubs'" )Line 9: conPubs.Open()Line 10: cmdSelectAuthors = New SqlCommand( "Select au_lname From Authors", conPubs )Line 11: dtrAuthors = cmdSelectAuthors.ExecuteReader()

Source File:C:\Inetpub\wwwroot\aspnet_unleashed\chapter9\sqldatareader.aspx Line:9
Stack Trace:

[SqlException: Login failed for user 'ROGER\ASPNET'.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 ASP.SqlDataReader_aspx.__Render__control1(HtmlTextWriter __output, Control parameterContainer) in C:\Inetpub\wwwroot\aspnet_unleashed\chapter9\sqldatareader.aspx:9 System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +27 System.Web.UI.Control.Render(HtmlTextWriter writer) +7 System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +243 System.Web.UI.Page.ProcessRequestMain() +1926

---

Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032

My Code is as below:
<%@. Page Language="VB" Debug="True" %>
<%@. import Namespace="System.Data.SqlClient" %>
<%
Dim conPubs As SqlConnection
Dim cmdSelectAuthors As SqlCommand
Dim dtrAuthors As SqlDataReader

conPubs = New SqlConnection( "server='(local)'; trusted_connection=true; database='pubs'" )
conPubs.Open()
cmdSelectAuthors = New SqlCommand( "Select au_lname From Authors", conPubs )
dtrAuthors = cmdSelectAuthors.ExecuteReader()
While dtrAuthors.Read()
Response.Write( "<li>" )
Response.Write( dtrAuthors( "au_lname" ) )
End While
dtrAuthors.Close()
conPubs.Close()
%>
Any idea what's wrong?

|||Yes. You either need to grant the windows ASPNET account privilleges for your SQL Server instance, or you need to get rid of the trusted_connection parameter and specify an SQL login (i.e. uid=sa; pwd=password;) in your connection string. I would go with the latter when using MSDE as it is alot easier.|||

IC...Finally, I managed to get it to work. Thanks a lot. Guys like you really make this community a wonderful place :)

|||Not a problem. Glad I could help.

MSDE Licensing

The MSDE licence says that it can be used for up to 5 concurrent users. My app is an ASP.NET app, so won't I just be using one user - the ASP.NET account? I'm wondering whether we can get away with just buying a new server and sticking MSDE on it rather than shelling out for another £5000 SQL server licence...I do not believe what you say (5 concurrent users) is correct.

http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp.

From this page:

Q. Can I use MSDE as a database for Web applications?

A. Yes, MSDE is an ideal solution for basic Web applications with up to 25 concurrent users.

There is throttling of performance with 8 concurrent OPERATIONS (but three are normally used internally).

Fromthis page:

The Microsoft® SQL Server? 2000 workload governor is designed to limit the performance of an instance of the database engine any time more than eight operations are active at the same time. An instance of the SQL Server 2000 database engine is one copy of the database software that operates as an operating system service.

The operations counted by the workload governor are:

Processing a request to open an inbound connection and login.

Processing a batch of one or more Transact-SQL statements received over an inbound connection.

Processing a distributed transaction operation, such as a prepare-to-commit or rollback operation.

Processing a request to log off and close an inbound connection.

Periodic system-generated operations such as shrinking a database if the database has the AUTO_SHRINK option turned on, completing the deletion of rows from the base level of indexes, or populating the SQL Server performance counters in the System Monitor.

Instances of SQL Server 2000 Personal Edition will also periodically generate system operations to process any full-text indexes referenced by the databases managed by the instance. SQL Server 2000 Desktop Engine (MSDE 2000) does not support full-text indexes.|||Sorry - missed a "2" out there.... Thats answered the question great - thanks a lot!

MSDE install for non-admin user

We are packaging our application installer with MSDE. Is there any
specific version of MSDE that we have to use so we don't require admin
users to perform the installation?
Thanks.
hi,
Papr1ka wrote:
> We are packaging our application installer with MSDE. Is there any
> specific version of MSDE that we have to use so we don't require admin
> users to perform the installation?
if you do not directly consume merge modules, the package is the same both
for MSDE Rel A and for the updated to sp 3a or 4 desktopengine package..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||We are call the sqlrunxx.msi during installation from MSDE 2000 SP3a.
And, if we do that using non-admin account, it won't work. I believe it
is because it needs to create the new service and everything for this
MSDE instance. Is there any other installation that we should use so we
don't required admin permission during installation?
|||hi,
Papr1ka wrote:
> We are call the sqlrunxx.msi during installation from MSDE 2000 SP3a.
> And, if we do that using non-admin account, it won't work. I believe
> it is because it needs to create the new service and everything for
> this MSDE instance. Is there any other installation that we should
> use so we don't required admin permission during installation?
not that I'm aware of... the install process need to set new services,
install and register lot of COM component... and eventually MDAC it self..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply