Hide Database Design

Hi

I wanted to see if Microsoft was going to address this problem

We as developers want to hide our database design (SQL Express DB)

We dont want to rely on the user rights settings to secure the design

Most of our clients have admin access which means that they can see the db design

We are happy for the design to be available on the server as change control should be given only on the server

I am perplexed how this hasnt been addressed

Your reply is appreciated

T

Hi T,

It may be perplexing to you, but this is a complex problem that was not part of the original design goals for SQL Server, or any server based DBMS really. Clearly the need you describe is becoming more important to many customers, and it is something that is being investigated for future versions of SQL Server.

In the mean time, you might want to consider SQL Server Compact Edition for your applications that require local data storage. SQL CE uses a password to protect the file directly. This allows you to embed the user name and password directly into your compiled application so user access to both the data and meta-data of your database are only allowed through your application.

SQL CE has some limitations: It only supports a subset of data types, it does not have support any programability (SProcs), it supports a subset of the standard T-SQL syntax and it only has the one user name and password, so everyone has the same level of access. SQL CE is also not suitable for multi-user applications. Many of these limitations can be overcome by using code logic in your application.

It is a trade-off, but SQL CE is a great light weight database that may be the right choice for you. You can find more information on MSDN and you should check out the SQL CE forum as well.

Mike

|||

Hi Mike

Thanks for that great answer

As long as we can setup replication as in SQL Express and manipulate the database

with a similar Management tool then it is a good solution and answer

How long do you think it will be before SQL Express is modified

Thanks

Touraj

|||

I don't really have a timeframe, sorry.

As far as replication, yes, SQL CE supports synchronizing data with a central SQL Server the same way SQL Express does. Starting with SP2 you will be able to manage SQL CE database using SQL Management Studio, the same tool used for SQL Server.

Mike

|||

Hello Touraj,

perhaps the Application Role is an option to do that? Then users have no permissions in the database. They only can log on. Only the application role has permissions to read and write the tables. (I don't know, perhaps it must be an own SQL Server instance for your application.)

But this does not prevent the users to copy the .mdf file and attach it to another SQL Server. So they can see all again. Or they can even open the .mdf file with notepad and see the table definitions with column names and the table data.

I asked for an optional obfuscation feature for SQL Server Express databases here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1094887&SiteID=1

When this feature would be implemented in such a way that an obfuscated database only can be attached when the encryption key of the obfuscation is provided in the attach command, then his attach by anyone could be prevented.

Regards Markus

sql

Hide Database Design

Hi

I wanted to see if Microsoft was going to address this problem

We as developers want to hide our database design (SQL Express DB)

We dont want to rely on the user rights settings to secure the design

Most of our clients have admin access which means that they can see the db design

We are happy for the design to be available on the server as change control should be given only on the server

I am perplexed how this hasnt been addressed

Your reply is appreciated

T

Hi T,

It may be perplexing to you, but this is a complex problem that was not part of the original design goals for SQL Server, or any server based DBMS really. Clearly the need you describe is becoming more important to many customers, and it is something that is being investigated for future versions of SQL Server.

In the mean time, you might want to consider SQL Server Compact Edition for your applications that require local data storage. SQL CE uses a password to protect the file directly. This allows you to embed the user name and password directly into your compiled application so user access to both the data and meta-data of your database are only allowed through your application.

SQL CE has some limitations: It only supports a subset of data types, it does not have support any programability (SProcs), it supports a subset of the standard T-SQL syntax and it only has the one user name and password, so everyone has the same level of access. SQL CE is also not suitable for multi-user applications. Many of these limitations can be overcome by using code logic in your application.

It is a trade-off, but SQL CE is a great light weight database that may be the right choice for you. You can find more information on MSDN and you should check out the SQL CE forum as well.

Mike

|||

Hi Mike

Thanks for that great answer

As long as we can setup replication as in SQL Express and manipulate the database

with a similar Management tool then it is a good solution and answer

How long do you think it will be before SQL Express is modified

Thanks

Touraj

|||

I don't really have a timeframe, sorry.

As far as replication, yes, SQL CE supports synchronizing data with a central SQL Server the same way SQL Express does. Starting with SP2 you will be able to manage SQL CE database using SQL Management Studio, the same tool used for SQL Server.

Mike

|||

Hello Touraj,

perhaps the Application Role is an option to do that? Then users have no permissions in the database. They only can log on. Only the application role has permissions to read and write the tables. (I don't know, perhaps it must be an own SQL Server instance for your application.)

But this does not prevent the users to copy the .mdf file and attach it to another SQL Server. So they can see all again. Or they can even open the .mdf file with notepad and see the table definitions with column names and the table data.

I asked for an optional obfuscation feature for SQL Server Express databases here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1094887&SiteID=1

When this feature would be implemented in such a way that an obfuscated database only can be attached when the encryption key of the obfuscation is provided in the attach command, then his attach by anyone could be prevented.

Regards Markus

hi, i need help on SQL, thanks

I'm doing a shopping cart using SQL Express and Visual Studio Web Developer on C#, ASP.NET

I recieved error when adding a order:

The variable name '@.oid' has already been declared. Variable names must be unique within a query batch or stored procedure.

The codes are:
comm = new SqlCommand("SELECT IDENT_CURRENT('Orders') as NewOrderID ");

comm.Connection = conn;
comm.Transaction = myTrans;

OrderID = Convert.ToInt32(comm.ExecuteScalar());

foreach (CartItem i in o.ItemList)
{
comm.CommandText = "INSERT INTO OrderDetail(OrderID, ProductID,Quantity, UnitPrice)VALUES (@.oid, @.pid, @.qty, @.price)";

comm.Parameters.AddWithValue("@.oid", OrderID);
comm.Parameters.AddWithValue("@.pid", i.ProductID);
comm.Parameters.AddWithValue("@.qty", i.Quantity);
comm.Parameters.AddWithValue("@.price", i.UnitPrice);
comm.Connection = conn;
comm.Transaction = myTrans;
comm.ExecuteNonQuery();
}

It seems that i can't add records into database with multiple loop.

Thanks in advance.

Well you want to only call once the insert for multiple items, that will optimize the code a little bit more.

Use TableAdapters, On the project right click and select Add New Item, select a DataSet and create a method to add multiple items!

|||

Try to clear parameters after each insert.

Add this to the end of your code block:

...........

comm.ExecuteNonQuery();

comm.Parameters.Clear();

}

|||

Thanksalbertpascual,

I'm not sure about using a TableAdapter, but does declaring the parameters outside the loop works?

I tried adding a dataset but with my limited 1month knowledge, I don't know how to complete the wizard or codes for the dataset.

if changing my existing codes works, it will be great.

thanks again,

|||

Hey, thanks alot Limno!!

but adding this sweet and simple "comm.Parameters.Clear();" it works..

thanks!!

Hi, Again

I'm very new to MS SQl Server, i Downloaded MS SQl Server express edition, the problem is i can't deal with it, when i downloaded (Microsoft SQL Server Management Studio Express - Community Technical Preview (CTP) November 2005) every time i try to install it , it give me error message says "The system administrator has set policies To Prevent this installation"
How can i over come this problem and install this interface to be able to deal with sql server?
is there any other software can do this job "making interface for the sql server express edition"?
ThxI downloaded the Nov CTP as well of the Management studio, but can't go beyond this error.

This installation package cannot be installed by the windows installer service. You must install a windows service pack that contains a newer version of the windows installer service.

I have Windows 2000 SP4 and also the Windows Installer 3.0, MSXML 6.0 Still

Thanks in advance for any help.
Kalyan.|||Go to the following link an install the latest update to Windows Installer: http://www.microsoft.com/downloads/details.aspx?FamilyID=889482fc-5f56-4a38-b838-de776fd4138c&DisplayLang=en

Cheers,
Dan|||hi,
I have the windows installer 3.1 but yet i can't install (TCP) managment studio keep getting the same error "administrator has set policies to prevent this installation"
additional hint : - i use windows xp_sp2 - when i used to counter this problem i downloaded MS Visual Web developer express 2005 and i was able to connect to sql server, but really i'm new to sql and i need to see interface to be able to learn it at least|||Sorry for the messege, i downloaded the SQl Server Management Studio Express Again and when i tried to install it , it worked seems it was a problem in the first time i downloaded it something was wrong in it
anyway thx guys

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set

Hi,

I have a problem with linked servers.

I have an application running against a SQLServer 2005 Express. For some limitations, I had to access from the same application to another database, but I cannot change to another server.

So I have 2 created a second instances, where the first one refers the second one and I created synonyms in the first one to access to all the objects in the second one, to emulate a database in the first instances, but running on the second one. The final idea is to move to another server, but for the testing I use another instance.

But when I try to access to the aplication database, I hav the following error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

I searched solutions for this issue, but I only found to add SET ANSI_NULLS ON and SET ANSI_WARNINGS ON to my connection, before the queries, but I can't, because I cannot change the application.

If anyone can help me, I'd be veri greatfull

Best regards, ArielAriel,
I had a similar problem with SQL 2000 when I added a stored procedure to access the data on a linked server. I had created the stored proc through Query Analyzer and the work around was to create the stored proc through Enterprise Manager. Once I created within Enterprise Manager it worked.
A google search of this problem will provide results.|||The problem is the application have a lot of SPs, tables and views I have created the synonims.

The problem is only with the SPs?

If that, I can remove the synonims and create SPs that access the original ones.

Thanks, Ariel

Here's the answer ... figured it out myself

I'm quite addicted to Patterns-&-Practices Enterprise.Library.Data module, but I can't get it to access a SQL Server Express Database. I've tried several different .CONFIG files and a unch of different settings and I keep getting:

The local database "XXYYZZ" is not defined in configuration.

I'm using Visual Studio 2005 C# in a WinForms application.

Can Microsoft.Practices.EnterpriseLibrary.Data access SQL Server Express Database?

<configuration>

<configSections>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null" />

</configSections>

<dataConfiguration defaultDatabase="Connection String" />

<connectionStrings>

<add name="myDb" connectionString="Database=Database;Server=(local)\SQLEXPRESS;AttachDbFilename=C:\myDir\myDBname.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

</configuration>

- and it's use

Database db = DatabaseFactory.CreateDatabase("myDb");
DbCommand dbC = db.GetStoredProcCommand("mySPname");
db.AddInParameter(dbC, "MyParameterName", DbType.Int32, MyIntVal);
DataSet ds = db.ExecuteDataSet(dbC);

Needs this stuff too ...

using System.Data;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.Practices.EnterpriseLibrary.Data;

using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

using System.Data.Common;

|||As the Elib cannot find the "mydb" entry, it seems that the ELIB is requesting the wrong (in terms of your thinking) .config file. See if you are pointing to the right config file.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Hem! Query Problem

I may not be using this right, so if I am not, be gentle

Im in Visual Basic Express, I went to the database explorer and right clicked on my table thats in the database and selected new query.

I noticed Query1 at the top, anyhow I laid out my query like I wanted, and went to save

Guess What, I couldnot find a save but, all I found was save all.

I did choose that, but I cannot find the query now.

Started a another query and I noticed ,Query2, so it had to have saved it, but where?

Is this not the right approach?

Very Confused at this point

Davids Learning

hi,

AFAIK, the "Save All" button is there for all other type of activities (vb/c# code) but not for the query itself..

the query you want to save can obviously not be saved on the SQL Server instance you are connected to as only procedures, views, udfs can be saved in the database metadata and not a "free text" query..

actually my thought is you should be prompted to save the query to a txt/sql file, but it seems not to be the case... perhaps you can try asking in VS2005 forum, but my thinking is the "free text" query can not be saved at all, as the "Save selected item" is grayed out..

regards

|||

Andrea is correct. The New Query functionality is used to allow you to run ad hoc queries against your database. I'm guessing that what you're actually looking for is the ability to save a new object in your database that you can later use in you application.

To do this, you would add the type of object that you want to add to your database, for example, for a simple select statement, you would add a new View. On the Data menu, point to Add New and then click View. Once you create your select statement, you will notcie that the Save Selected Item is available and when clicked you will have the oportunity to name your new View. The new View will show up in the View folder of your database.

Hope this helps.

Mike

Help: error when trying to connect to server using sql server 2005 express

Dear all,

I have installed sql server 2005 Express with SQLADV.exe. But when I open Management Studio Express it has error:

Can not connect to server

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error locating Server/Instance Specified).

I searched on google but did not find any solution. Anyone know about that, please help me.

check this link http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

there are few things to be done to access database engine

(a) Remote connection to be enabled

(b) Create exception if firewall is enabled on this srever

(c) start SQL Browser service

(d) check the protocols

Madhu

|||

Dear Madhu K Nair,

I just use my local computer to test, how can I check for protocol and should I do it. Now I just use my computer to create a website and database in my computer too.

|||

check this http://support.microsoft.com/kb/914277

Madhu

|||

Please check my blog for this:

http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx