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

Hidden Cascading parameters error with postback/refresh?

Hi

I have a report with a few cascading parameters. In my code I pass the value of the parameter to the report and set the flag to hide the parameter to true (isAdministrator).

ReportParameter dataEntity = new ReportParameter("DataEntity", "1", !isAdministrator);

Report.SetParameters(new ReportParameter[] { dataEntity } );

This works fine but as soon as I change any of the parameters the control viewer refreshes and loses my default value of the hidden parameter and sets it to the default I have set on the report when I designed it.

If I do not hide the "DataEntity" parameter the refresh works fine.

I need to hide this parameter or disable it so that the user can not change the value if they do not have permistion.

I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||

HI,

I am having the same problem. Have you found a resolution yet?

Thanks,

|||No, in the end I am not hiding the parameter.

Hidden Cascading parameters error with postback/refresh?

Hi

I have a report with a few cascading parameters. In my code I pass the value of the parameter to the report and set the flag to hide the parameter to true (isAdministrator).

ReportParameter dataEntity = new ReportParameter("DataEntity", "1", !isAdministrator);

Report.SetParameters(new ReportParameter[] { dataEntity } );

This works fine but as soon as I change any of the parameters the control viewer refreshes and loses my default value of the hidden parameter and sets it to the default I have set on the report when I designed it.

If I do not hide the "DataEntity" parameter the refresh works fine.

I need to hide this parameter or disable it so that the user can not change the value if they do not have permistion.

I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||

HI,

I am having the same problem. Have you found a resolution yet?

Thanks,

|||No, in the end I am not hiding the parameter.

Hidden Cascading parameters error with postback/refresh?

Hi

I have a report with a few cascading parameters. In my code I pass the value of the parameter to the report and set the flag to hide the parameter to true (isAdministrator).

ReportParameter dataEntity = new ReportParameter("DataEntity", "1", !isAdministrator);

Report.SetParameters(new ReportParameter[] { dataEntity } );

This works fine but as soon as I change any of the parameters the control viewer refreshes and loses my default value of the hidden parameter and sets it to the default I have set on the report when I designed it.

If I do not hide the "DataEntity" parameter the refresh works fine.

I need to hide this parameter or disable it so that the user can not change the value if they do not have permistion.

I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||

HI,

I am having the same problem. Have you found a resolution yet?

Thanks,

|||No, in the end I am not hiding the parameter.|||Can anyone shed some light on this? Is this a bug?
I am having same problem as the parameter does not refresh if some other parameter is changed.

For example if I change the event in the list box, the appropriate begin and end date is not modified?

hi

Hi
I am new in the SQL server field and i tried to solve the problem of
deadlocking.
Please help me in resolving the deadlock in step by step process.
Thank you
ABiAbi
Since you have not mentioend what is the version are you using?
http://blogs.msdn.com/bartd/archive.../09/747119.aspx
"Abi" <abi.chapagai@.gmail.com> wrote in message
news:1159853389.087757.64370@.m7g2000cwm.googlegroups.com...
> Hi
> I am new in the SQL server field and i tried to solve the problem of
> deadlocking.
> Please help me in resolving the deadlock in step by step process.
>
> Thank you
> ABi
>|||Boy that is a pretty vanilla request there. The topic of deadlock
resolution can go from database design to proper indexing to locking levels,
etc.
I think you would best learn from the hundreds of articles and websites that
already cover this topic on the Internet.
"Abi" <abi.chapagai@.gmail.com> wrote in message
news:1159853389.087757.64370@.m7g2000cwm.googlegroups.com...
> Hi
> I am new in the SQL server field and i tried to solve the problem of
> deadlocking.
> Please help me in resolving the deadlock in step by step process.
>
> Thank you
> ABi
>|||Hi,
Go thru this URL. This say the methods to troubleshoot deadlocking.
http://www.sql-server-performance.com/deadlocks.asp
Thanks
Hari
"Abi" <abi.chapagai@.gmail.com> wrote in message
news:1159853389.087757.64370@.m7g2000cwm.googlegroups.com...
> Hi
> I am new in the SQL server field and i tried to solve the problem of
> deadlocking.
> Please help me in resolving the deadlock in step by step process.
>
> Thank you
> ABi
>|||sp_who2
"Abi" <abi.chapagai@.gmail.com> wrote in message
news:1159853389.087757.64370@.m7g2000cwm.googlegroups.com...
> Hi
> I am new in the SQL server field and i tried to solve the problem of
> deadlocking.
> Please help me in resolving the deadlock in step by step process.
>
> Thank you
> ABi
>|||There are actually a couple better than sp_who and sp_who2 at:
http://www.sqlservercentral.com/Scr...20&categoryid=1
I've used the new sp_who3 and sp_what (my favorite). Check 'em out - very
nice.
"Mike C#" <xyz@.xyz.com> wrote in message
news:krFUg.778$iS5.121@.newsfe11.lga...
> sp_who2
>
> "Abi" <abi.chapagai@.gmail.com> wrote in message
> news:1159853389.087757.64370@.m7g2000cwm.googlegroups.com...
>

HelpColumnar Report with 3 Groupings?

Hi

I have sales data that needs to be grouped by Company, State and
Date. I would like to format the report as shown in image.

I am using Crystal Reports XI. I am new to Crystal Reports and
would like to find out whether this report is possible using either
columnar format or cross-tab?

Many ThanksPost a sample of your table(s). I can't help without knowing what you have to start with.

I have a feeling you'll need a crosstab|||Hi

I have data in two tables - Sales and Dealers.

Sales
-------
DealerCode
WeekEnding
NumberSold

Dealers
-------
DealerCode
CompanyName
State

I have a command in Crystal as follows:

SELECT d.CompanyName, d.State, s.WeekEnding, s.NumberSold
FROM sales s
LEFT JOIN dealers d
ON s.DealerCode = d.DealerCode
AND s.WeekEnding BETWEEN '2006-04-01' AND '2006-04-30'

I would like to group the data returned by CompanyName, State and Date to get a Total of NumberSold.

I would like to format the report in columns (as in image attachment). I would also like to have a two week total and a week-on-week figure.

If you need any further info, let me know. Thanks for your help!|||Look for cross tab reports in help file

Help; ASP web site, IIS 6.0, Server 2003, SQL server 2000

Hi
I,m new to Server 2003 and I'm moving an ASP website to it.
This is what I have; an basic ASP web site that connects to a "SQL server
2000" database on Windows XP box, using IIS.
Now I have moved the site to Server 2003, on IIS 6, but I'm still using "SQL
Server 2000"
However I can't get the website to connect to the database. this is the
error I'm am geting
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied.
/uoc/include/Common.asp, line 100
In IIS 6.0, under the Directory Security tab, I'm using anonymous access,
and it uses the user IUSR_<computername>. Also I have Integrated Windows
authentication checked off. Is there some security setting that I need to
set, or do I need to give more access to the "IUSR" user? (oh and I also
confirmed that SQL server is using SQL Server and Windows Authentication )
Thanks for any help you can give me.
http://spaces.msn.com/members/anthonyarms/
is there an OLEDB connection string in that Common.asp page? it sounds
like thats your connection string. you may need to modify it
accordingly.
|||Thanks for the reply
My connection looks like this :
Application("Connection_ConnectionString") = "Provider=SQLOLEDB.1;Persist
Security Info=False;User ID=[database_name];Initial Catalog=UOC;Data
Source=[computer_name];Use Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=[computer_name];Use Encryption for Data=False;Tag
with column collation when possible=False;User
Id=[user_name];PASSWORD=[password];"
I don't know if I need to change anything on Server 2003 (the only things I
changed is the text in the square brackets )
Do you think it could be something else?
thanks for any incite you can provide
http://spaces.msn.com/members/anthonyarms/
"GlennThomas5" wrote:

> is there an OLEDB connection string in that Common.asp page? it sounds
> like thats your connection string. you may need to modify it
> accordingly.
>

Help; ASP web site, IIS 6.0, Server 2003, SQL server 2000

Hi
I,m new to Server 2003 and I'm moving an ASP website to it.
This is what I have; an basic ASP web site that connects to a "SQL server
2000" database on Windows XP box, using IIS.
Now I have moved the site to Server 2003, on IIS 6, but I'm still using "SQL
Server 2000"
However I can't get the website to connect to the database. this is the
error I'm am geting
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access
denied.
/uoc/include/Common.asp, line 100
In IIS 6.0, under the Directory Security tab, I'm using anonymous access,
and it uses the user IUSR_<computername>. Also I have Integrated Windows
authentication checked off. Is there some security setting that I need to
set, or do I need to give more access to the "IUSR" user? (oh and I also
confirmed that SQL server is using SQL Server and Windows Authentication )
Thanks for any help you can give me.
--
http://spaces.msn.com/members/anthonyarms/Try adding a SQL Server UID and PWD to the connection string.
"ARMS" <anthonyarms@.gmail.com> wrote in message
news:289D8794-31E0-4F67-BC55-F07E18A0875C@.microsoft.com...
> Hi
> I,m new to Server 2003 and I'm moving an ASP website to it.
> This is what I have; an basic ASP web site that connects to a "SQL server
> 2000" database on Windows XP box, using IIS.
> Now I have moved the site to Server 2003, on IIS 6, but I'm still using
"SQL
> Server 2000"
> However I can't get the website to connect to the database. this is the
> error I'm am geting
> Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist o
r access
> denied.
> /uoc/include/Common.asp, line 100
> In IIS 6.0, under the Directory Security tab, I'm using anonymous access,
> and it uses the user IUSR_<computername>. Also I have Integrated Windows
> authentication checked off. Is there some security setting that I need to
> set, or do I need to give more access to the "IUSR" user? (oh and I also
> confirmed that SQL server is using SQL Server and Windows Authentication )
> Thanks for any help you can give me.
> --
> http://spaces.msn.com/members/anthonyarms/

Help; ASP web site, IIS 6.0, Server 2003, SQL server 2000

Hi
I,m new to Server 2003 and I'm moving an ASP website to it.
This is what I have; an basic ASP web site that connects to a "SQL server
2000" database on Windows XP box, using IIS.
Now I have moved the site to Server 2003, on IIS 6, but I'm still using "SQL
Server 2000"
However I can't get the website to connect to the database. this is the
error I'm am geting
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied.
/uoc/include/Common.asp, line 100
In IIS 6.0, under the Directory Security tab, I'm using anonymous access,
and it uses the user IUSR_<computername>. Also I have Integrated Windows
authentication checked off. Is there some security setting that I need to
set, or do I need to give more access to the "IUSR" user? (oh and I also
confirmed that SQL server is using SQL Server and Windows Authentication )
Thanks for any help you can give me.
http://spaces.msn.com/members/anthonyarms/
Try adding a SQL Server UID and PWD to the connection string.
"ARMS" <anthonyarms@.gmail.com> wrote in message
news:289D8794-31E0-4F67-BC55-F07E18A0875C@.microsoft.com...
> Hi
> I,m new to Server 2003 and I'm moving an ASP website to it.
> This is what I have; an basic ASP web site that connects to a "SQL server
> 2000" database on Windows XP box, using IIS.
> Now I have moved the site to Server 2003, on IIS 6, but I'm still using
"SQL
> Server 2000"
> However I can't get the website to connect to the database. this is the
> error I'm am geting
> Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
> denied.
> /uoc/include/Common.asp, line 100
> In IIS 6.0, under the Directory Security tab, I'm using anonymous access,
> and it uses the user IUSR_<computername>. Also I have Integrated Windows
> authentication checked off. Is there some security setting that I need to
> set, or do I need to give more access to the "IUSR" user? (oh and I also
> confirmed that SQL server is using SQL Server and Windows Authentication )
> Thanks for any help you can give me.
> --
> http://spaces.msn.com/members/anthonyarms/

Help; ASP web site, IIS 6.0, Server 2003, SQL server 2000

Hi
I,m new to Server 2003 and I'm moving an ASP website to it.
This is what I have; an basic ASP web site that connects to a "SQL server
2000" database on Windows XP box, using IIS.
Now I have moved the site to Server 2003, on IIS 6, but I'm still using "SQL
Server 2000"
However I can't get the website to connect to the database. this is the
error I'm am geting
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access
denied.
/uoc/include/Common.asp, line 100
In IIS 6.0, under the Directory Security tab, I'm using anonymous access,
and it uses the user IUSR_<computername>. Also I have Integrated Windows
authentication checked off. Is there some security setting that I need to
set, or do I need to give more access to the "IUSR" user? (oh and I also
confirmed that SQL server is using SQL Server and Windows Authentication )
Thanks for any help you can give me.
--
http://spaces.msn.com/members/anthonyarms/is there an OLEDB connection string in that Common.asp page? it sounds
like thats your connection string. you may need to modify it
accordingly.|||Thanks for the reply
My connection looks like this :
Application("Connection_ConnectionString") = "Provider=SQLOLEDB.1;Persist
Security Info=False;User ID=[database_name];Initial Catalog=UOC;Data
Source=[computer_name];Use Procedure for Prepare=1;Auto Translate=True;P
acket
Size=4096;Workstation ID=[computer_name];Use Encryption for Data=False;T
ag
with column collation when possible=False;User
Id=[user_name];PASSWORD=[password];"
I don't know if I need to change anything on Server 2003 (the only things I
changed is the text in the square brackets )
Do you think it could be something else?
thanks for any incite you can provide
--
http://spaces.msn.com/members/anthonyarms/
"GlennThomas5" wrote:

> is there an OLEDB connection string in that Common.asp page? it sounds
> like thats your connection string. you may need to modify it
> accordingly.
>