Determine security access to stored procedure through ASP

I have an ASP application running in a MS Windows Server 2003 computer
joined to a Windows 2000 Active Directory domain.
Different users have different roles, and the security in the SQL
database is based on Active Directory security groups (SQL server is
configured for Windows security and not SQL server security).
I would like to make certain ASP page show an icon depending on whether
the user has EXECUTE permission on a particular stored procedure.
Example: User X@.domain.com is a member of the Active Directory
security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
in of a SQL user called "App Admins", and App Admins have been granted
EXECUTE permission to the stored procedure spDeleteSomething. I want
the ASP to determine if X@.domain.com has the permission on
spDeleteSomething so an icon is displayed; in this case it should be
displayed.
I hope I made this clear, but if not, feel free to ask for more details.You can use the IS_MEMBER function to check to see if the current login is a
member of the App Admins group:
SELECT IS_MEMBER('domain.com\App Admins')
returns 1 if the current login is a member of the App Admins security group,
0 if they aren't.
"webJose" wrote:

> I have an ASP application running in a MS Windows Server 2003 computer
> joined to a Windows 2000 Active Directory domain.
> Different users have different roles, and the security in the SQL
> database is based on Active Directory security groups (SQL server is
> configured for Windows security and not SQL server security).
> I would like to make certain ASP page show an icon depending on whether
> the user has EXECUTE permission on a particular stored procedure.
> Example: User X@.domain.com is a member of the Active Directory
> security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
> in of a SQL user called "App Admins", and App Admins have been granted
> EXECUTE permission to the stored procedure spDeleteSomething. I want
> the ASP to determine if X@.domain.com has the permission on
> spDeleteSomething so an icon is displayed; in this case it should be
> displayed.
> I hope I made this clear, but if not, feel free to ask for more details.
>|||In ASP.NET, you can determine if a web user is a member of an Active
Directory group or role without going through SQL Server.
How To: Use Role Manager in ASP.NET 2.0
http://msdn.microsoft.com/library/d... />
000013.asp
For example:
if (Roles.IsUserInRole("TestRole"))
{
Label1.Text = User.Identity.Name + " is in role TestRole";
}
else
{
Label1.Text = User.Identity.Name + " is NOT in role TestRole";
}
"webJose" <webJose@.gmail.com> wrote in message
news:1140194004.035980.226530@.g14g2000cwa.googlegroups.com...
>I have an ASP application running in a MS Windows Server 2003 computer
> joined to a Windows 2000 Active Directory domain.
> Different users have different roles, and the security in the SQL
> database is based on Active Directory security groups (SQL server is
> configured for Windows security and not SQL server security).
> I would like to make certain ASP page show an icon depending on whether
> the user has EXECUTE permission on a particular stored procedure.
> Example: User X@.domain.com is a member of the Active Directory
> security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
> in of a SQL user called "App Admins", and App Admins have been granted
> EXECUTE permission to the stored procedure spDeleteSomething. I want
> the ASP to determine if X@.domain.com has the permission on
> spDeleteSomething so an icon is displayed; in this case it should be
> displayed.
> I hope I made this clear, but if not, feel free to ask for more details.
>|||JT: Thank you for your response. Although highly enlighting, I am not
using .NET (I know! I should be). :-)
Mark: Thank you for your response. IS_MEMBER workS OK for me. I'll
create user-defined functions to encapsulate this functionality. Now,
out of curiosity, is there a way to test for EXECUTE permissions on any
stored procedure like on the fly? For example, something like:
If CanExecute("spSomeSP") Then
Response.Write "You got it!"
End If
And CanExecute() would test somehow the permissions for the user ID on
the sp name passed as argument.|||You could query it from the sysprotects system table.
For SQL 2000:
IF EXISTS (
SELECT 1 FROM sysprotects
WHERE [id] = OBJECT_ID('yourproc')
AND [uid] = USER_ID()
AND [action] = 224
AND [protecttype] IN (204,205)
)
BEGIN
PRINT 'You have access'
END
For SQL 2005
IF EXISTS (
SELECT 1 FROM sys.database_permissions
WHERE [class] = 1
AND [major_id] = OBJECT_ID('yourproc')
AND [grantee_principal_id] = USER_ID()
AND [type] = 'EX'
AND [state] IN ('G','W')
)
BEGIN
PRINT 'You have access'
END
"webJose" wrote:

> JT: Thank you for your response. Although highly enlighting, I am not
> using .NET (I know! I should be). :-)
> Mark: Thank you for your response. IS_MEMBER workS OK for me. I'll
> create user-defined functions to encapsulate this functionality. Now,
> out of curiosity, is there a way to test for EXECUTE permissions on any
> stored procedure like on the fly? For example, something like:
> If CanExecute("spSomeSP") Then
> Response.Write "You got it!"
> End If
> And CanExecute() would test somehow the permissions for the user ID on
> the sp name passed as argument.
>|||One caveat to this: the will only return that the user has access if the use
r
they map too was explicity given access to execute procedure. If their
permission is inherited from membership in a server or database role, my
script will not show them as having access.
If you are explicitly giving execute permission to each of the users, then
it will work.
"Mark Williams" wrote:
> You could query it from the sysprotects system table.
> For SQL 2000:
> IF EXISTS (
> SELECT 1 FROM sysprotects
> WHERE [id] = OBJECT_ID('yourproc')
> AND [uid] = USER_ID()
> AND [action] = 224
> AND [protecttype] IN (204,205)
> )
> BEGIN
> PRINT 'You have access'
> END
> For SQL 2005
> IF EXISTS (
> SELECT 1 FROM sys.database_permissions
> WHERE [class] = 1
> AND [major_id] = OBJECT_ID('yourproc')
> AND [grantee_principal_id] = USER_ID()
> AND [type] = 'EX'
> AND [state] IN ('G','W')
> )
> BEGIN
> PRINT 'You have access'
> END
> --
> "webJose" wrote:
>

Determine if more than one row returned

I have an ASP/ADO application querying an SQL Server DB. I want know the
most efficient way to determine if more than one row is returned from a
query. If more than one row is returned, the user will be presented with a
choice of which row to process. If only one row is returned, I want to skip
this stage, and process that single row immediately.
I can think of a number of ways of acheiving this (eg. .recordcount) but I'm
looking for the slickest and most efficient method.
Any thoughts or suggestions
Thanks
CJMCJM wrote:
> I have an ASP/ADO application querying an SQL Server DB. I want know
> the most efficient way to determine if more than one row is returned
> from a query. If more than one row is returned, the user will be
> presented with a choice of which row to process. If only one row is
> returned, I want to skip this stage, and process that single row
> immediately.
> I can think of a number of ways of acheiving this (eg. .recordcount)
> but I'm looking for the slickest and most efficient method.
> Any thoughts or suggestions
>
Best option: Use a stored procedure that only returns the data if more
than one row meeting the criteria exist.
Second best:
Use GetRows to put the recordset data into an array and check its upper
index bound using the ubound function.
This has the added benefit of allowing you to:
1. use the efficient server-side forward-only cursor (which does not
support recordcount)
2. immediately close and destroy the recordset
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hi,
Thanks for your post!
My understanding of your issue is:
You wanted the most efficient way to retrieve the result row count.
If I have misunderstood, please let me know.
I recommend you write a common stored procedure retrieving the row count of
any query result.
Here is a sample just for reference:
create procedure proc_getqueryrowscount
(
@.strquery varchar(1000)
)
as
declare @.strcountquery varchar(1000)
select @.strcountquery = 'select count(*) from ( ' + @.strquery + ' ) v '
exec ( @.strcountquery )
In your application, design and implement a common application interface
function, such as:
int GetDBQueryCount(String strQuery)
In any place when you need to get a query result row count, it's convenient
to use this function.
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 w 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.|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:O$GUbesmGHA.1596@.TK2MSFTNGP04.phx.gbl...
> Best option: Use a stored procedure that only returns the data if more
> than one row meeting the criteria exist.
> Second best:
> Use GetRows to put the recordset data into an array and check its upper
> index bound using the ubound function.
> This has the added benefit of allowing you to:
> 1. use the efficient server-side forward-only cursor (which does not
> support recordcount)
> 2. immediately close and destroy the recordset
>
Bob,
Thanks for the response...
Option 2 is the best in this case... there may only be one row returned
(which is fine) but in this case I need to handle it differently...
Thanks
Chris|||Hi, Chris,
I'm glad to see Bob's advice suited you.
If you have any other concerns, please don't hesitate to contact us.
Thanks for using Microsoft Newsgroup.
Have a good 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 w 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.|||CJM wrote:
> "Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
> news:O$GUbesmGHA.1596@.TK2MSFTNGP04.phx.gbl...
> Bob,
> Thanks for the response...
> Option 2 is the best in this case... there may only be one row
> returned (which is fine) but in this case I need to handle it
> differently...
>
Why not handle it in the stored procedure?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||CJM wrote:
> "Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
> news:O$GUbesmGHA.1596@.TK2MSFTNGP04.phx.gbl...
> Thanks for the response...
> Option 2 is the best in this case... there may only be one row
> returned (which is fine) but in this case I need to handle it
> differently...
>
Just to expand, something like this:
create procedure ...
declare @.rows int
set @.rows=(select count(*) from table where <criteria> )
if @.rows=0
do something
return
if @.rows = 1
do something else
return
if @.rows > 1
select <columns> from table where <criteria>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OhFw993mGHA.2264@.TK2MSFTNGP04.phx.gbl...
> Just to expand, something like this:
> create procedure ...
> declare @.rows int
> set @.rows=(select count(*) from table where <criteria> )
> if @.rows=0
> do something
> return
> if @.rows = 1
> do something else
> return
> if @.rows > 1
> select <columns> from table where <criteria>
>
It's client-side stuff...need user interaction...
If no rows returned, display error message
If 1 row returned, immediately go to editing page for that row...
If several rows returned, display list. User picks row and it redirects to
editing page...
CJM|||Hi CJM,
If there are at most 2 values, then most methods will probably be
equally fast. However, if there are cases where there are much more than
2 values, then the solution below is probably the fastest. This is
because query execution will end when the second value is found.
Note however, that it is based on the proprietary behavior of SQL Server
that does not require just one row/value to be assigned to a local
variable.
Declare @.the_value int
SELECT TOP 2 @.the_value = value
FROM MyTable
WHERE some_column = 'some value'
If @.@.rowcount > 1
Begin
print 'Please select a value'
..
End
Else
SELECT @.the_value AS "This is the value"
HTH,
Gert-Jan
CJM wrote:
> I have an ASP/ADO application querying an SQL Server DB. I want know the
> most efficient way to determine if more than one row is returned from a
> query. If more than one row is returned, the user will be presented with a
> choice of which row to process. If only one row is returned, I want to ski
p
> this stage, and process that single row immediately.
> I can think of a number of ways of acheiving this (eg. .recordcount) but I
'm
> looking for the slickest and most efficient method.
> Any thoughts or suggestions
> Thanks
> CJM

determine if autoincrement is set in a table, form asp.net

I want to access sqlserver table properties from asp.net .

How do i know that a table defined in sqlserver has autoincrement field or not.

Actually i have to access all the tables in a database and execute different function for table with auto increment on and off.

I am not being able identify this property from codes.
Please help.
thank you

Hey,

When you connect to the database, you can't know that from ADO.NET; you have to recreate identities manually on the ADO.NET side. However, if you have SQL Server 2005 API's installed, you can use the SMO objects to connect to a 7.0, 2000, or 2005 database and find this information out.

|||Thanks brian

I am using sql 2000 and SqlDataAdapter object

I think the following code should work but its not working.
any ideas

Dim dsAs DataSet ' = ( got dataset from a table)
Dim hasAutoIncrementAsBoolean =FalseForEach columnAs DataColumnIn ds.Tables(0).Columns If column.AutoIncrementThen

hasAutoIncrement =

True
ExitFor EndIfNext|||

Hey,

The problem is there isn't any autoincrement returned back from the dataset; you have to establish that programatically. So you need to do:

dataSet.Tables(0).Columns(0).AutoIncrement = true
dataSet.Tables(0).Columns(0).AutoIncrementStep = 1

Etc. You have to set this up by yourself; it doesn't pull it back for you.

|||

SELECT *,COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsIdentity')AS IS_IDENTITYFROM INFORMATION_SCHEMA.COLUMNS

|||

Which would lead you to this query:

SELECT COUNT(*)

FROM (

SELECT

*,COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsIdentity')AS IS_IDENTITY

FROM

INFORMATION_SCHEMA.COLUMNS

) t1

WHERETABLE_NAME=@.MyTable AND IS_IDENITITY=1

|||Thank you very muchMotley

exactly what i was looking for.

Thanks

Detach and Attach functions in SQL Server 2005

Hi,

I'm trying to port my ASP.NET web application to the production system.

I'm connection to the SQL Server 2005 instance on my hosting server via CTP. I've uploaded the .mdf and .ldf files of my DB via FTP to the hosting server, and then tried to attach using this command:

use master gosp_attach_db'tgp','F:\webspace\disk20\db\TGP.mdf','F:\webspace\disk20\db\TGP_log.ldf' go

but then there is an error (obviously) stating that I don't have permissions to create a database in database master.

I must admit that I'm prettycluelessin this area. My hostingservicesalready created a "place holder" for my database (tgp), but I don't know how to proceed from here in order attach the database files in the production environment. Is this something I can do myself, or must I involve the hosting services?

Thanks,

Alon

sp_attach_db requires the same permissions as CREATE DATABASE - so you're likely not going to be able to do this (if so, let me know who your host is - I could use the free/extra space they'd let me set up *grin*).

You'll either need to contact them and have them hook up your DB, or look into using user-instance/attachable SQL Express functionality.|||

You have two options just backup your database and use management studio to restore your database on the host SQL Server after you have registered the host SQL Server in your management studio. In the backup and restore wizard choose the restore from device option. The other option is try the thread below for Attach database code modify it for your needs and use it. Hope this helps.

http://forums.asp.net/thread/981274.aspx

|||

Thak you for your reply.

I tried to backup/restore, but when I tried to point to the location of the backup file (on the remote server), got the error message that I'm not authorized... I've just sent a request from the hosting firm to handle this.

I have a general question: what are the guidelines when moving from the test/development system to the production system? I couldn't find any article summarizing the process.

Thank you,

Alon

|||You should only use a company that gives you access to management studio so you can use the backup and restore wizard because if you don't backup your database they will charge you to back it up. Here in the US most hosting company will give you the access. Hope this helps.|||

Yep, this helps

Alon

Detach & Attach Database

I've just created ASPNETDB database with ASP.NET Security. Now, I want to send this db to orther computer.First, I detached this db, then when I used attach database in that computer, there is an error :

Error 602: Could not find row in sysindexes for database ID 8, object ID 1, indext ID 1. RUN DBCC CHECKTABLE on sysindexes.

Please help me .Thank.

I guess you're trying to attach a database from SQL2005/Express to a SQL2000 instance. Since most system objects have been changed in SQL2005, you can't use a SQL2005 database in SQL2000. What you can do is to transfer database objects/data/schema from SQL2005 to SQL2000.

|||How I do ? Could you guide me step by step ! Thank.|||You can open VS2005->new an Integration Service Project->add a Transfer SQL Server Objects Task

Design question

We currently have an ASP.NET app that includes some reports. I'm
considering
making the data available in RS and I'm looking for some opinions
about
the feasibility and difficulty in doing this.
The app uses ODBC to connect to DB2. The users use their DB2 userid
and password, and this is what is used in the connection string.
The current solution includes a lot of manipulation of datatables
which are then bound to ASP.NET datagrids.
My thought was to create a web service that would serve as an RS Data
Extension, but I'm just taking a first look at this and don't know how
practical it would be. The idea is that the data retrieval and
manipulation logic could be in the web service, which would return a
dataset (or whatever).
The solution would have to include drilling down / linking to other
reports, so the userid and password would have to be maintained behind
the scenes so they could be passed, along with other parameters, to
the web service for each report/subreport.
Any thoughts are welcome.
TIA,
JimSounds like a winner to me :-). It doesn't have to be a web service of
course, you can write a data extension which would itself query your data
source and massage the data.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"jim corey" <jhcorey@.yahoo.com> wrote in message
news:1c4f8dcf.0409210702.587ac17d@.posting.google.com...
> We currently have an ASP.NET app that includes some reports. I'm
> considering
> making the data available in RS and I'm looking for some opinions
> about
> the feasibility and difficulty in doing this.
> The app uses ODBC to connect to DB2. The users use their DB2 userid
> and password, and this is what is used in the connection string.
> The current solution includes a lot of manipulation of datatables
> which are then bound to ASP.NET datagrids.
> My thought was to create a web service that would serve as an RS Data
> Extension, but I'm just taking a first look at this and don't know how
> practical it would be. The idea is that the data retrieval and
> manipulation logic could be in the web service, which would return a
> dataset (or whatever).
> The solution would have to include drilling down / linking to other
> reports, so the userid and password would have to be maintained behind
> the scenes so they could be passed, along with other parameters, to
> the web service for each report/subreport.
> Any thoughts are welcome.
> TIA,
> Jim

design of a process

I'm going to design a web page for students parking permit application.I'm new to asp.net, I have a process in mind doing it like this:

The form asks student to choose a permit type, then fill in applicant info, like name, grade, vehicle info, student may have 2 vehicles, and payment method, check or credit card,

I plan to create 3 tables like tblPermit, tblapplicant, tblVehicle.

After the students fill in the form, click submit button, I pass the values and call a stored procedure, some thing like this.Dim CmdAsNew Data.SqlClient.SqlCommand(MySQL, MyConn)

Cmd.CommandType = Data.CommandType.StoredProcedure

Then in the stored proceudre, I will do :

Begin

insert into Permit table, then get permitID, by using something like this:

Select @.PermitID=@.@.Identity

Then insert into Student table,then use

Select @.StudentID=@.@.Identity

Then insert into vehicle table.

All the above 3 processes I put it in begin, end--So that they will not messed up with other applicants data.

I think begin...end is one transaction.

SO am I doing correctly this way? Thanks

Begin...End doesn't signify a transaction, it signifies a code block. The idea is basically correct, but use SELECT @.PermitID=SCOPE_IDENTITY() instead. You should also wrap the whole thing in a transaction to be safe.

|||

So shall I use something like this:

AS
Begin

Begin transaction
Set NoCount on
DECLARE @.PermitID INT
Insert table............

Commit Transaction

End

Can I use the begin transaction ...commit transaction in Begin ......end, or no need of begin..end.

Thanks

design ideas

I'm new to asp.net. Please help me with some ideas about the design of databases and interface of the web site.

We are a public school district, I want to create a website for families to go on line to update their family and student's demographic information. Basically I put the students' infomation pulled from our student information system software on the web, the parents can log in on line to update it at the beginning of school year. After that, I will create some report and let secretary manually do the update to our student information system.

The demographic infor includes 3 parts,

1. family street address, city, state, zip
2 guardian1 primary phones,second phone, emails. primary phones,second phone
3, student date birth, gender. may have multiple students in one family

But how can I track which field parents changed, shall I do it in programming in the web form, or later when I create some kind of reports. For I only want to pull students with the fields that updated and give them to secretary to update manully, and I don't want to generate a full report and let them compare which field is changed and then update, it will take them too much time.

Thanks much in advance

Hello anncao,

> how can I track which field parents changed

the exact implementation depends on your programming skills, so i'll be starting from a basic approach, which should anyway be enough in your scenario:

1) The database: to each field should be associated a flag field (say bit type), telling wether the field has been changed or not (say "street" and "street_changed"); also another flag field should be associated to the record as a whole, to tell if *any* of the fields has been changed (say "rec_changed"). These flags should default to 0 (zero), and be set to 1 when needed.

2) The input form: before any rendering, you should store current field values, possibly in ViewState; after postback and before writing to the database, you should compare each field with its orginal stored value, and set its associated flag field to 1 if it has changed; also you should set the record-level flag in case *any* of the fields have changed. Please note that you will be writing to the database only flags set to 1, and be sure not to overwrite flags already set to 1 even in case in the current postback the user has not changed them...

3) The report: your final report should query the database and retrieve only those records where the record-level flag is set to 1; also, it should show changed fields somewhere marked (say red) in case their associated flag is set to 1 (or you could just show changed fields, or anything; "readability" is what matters here).

Hope this is clear enough. Btw, you could optimize on the flags and make them a single integer field where you work bit-wise level, but if this is too much complexity for you, just don't bother about it, as there's no relevant optimization at this level. Just make the code "readable" as well... ;)

-LV

|||

Thank you very much, that's certainly a good idea. But I still feels hard. I'm new to asp.net, try to use visual web developer express to accomplish this, thinking use the 3 gridview for the 3 parts, and have a update button for each part. for student and family are in different tables.

Is there an easier way I can do it similarly in visual web developer.

or can i make a copy of the tables and then later to compare them? but don't know how to select those fields which are different? we only collect his at the beginning of school year.

Or can you suggest me a book that I can start with how to write those codes ?

I have some experience with VBA, but not asp.net.

Thanks

|||

Ignore my previous message, I will go with the way you suggested.

For example I have a family table.if I want to do the way as you described at your posting, do I need to create an object data source, and where shall I write the comparing code?

Thanks

Design Help – Access SQL Database behind a firewall from ASP .NET pages.

Hi,

I am new to internet development and would like some advice on the technology used to access a SQL database that sits on a network behind a firewall.

** ASP .NET Page ** -> ** Web Server ** -> ** FIREWALL ** -> ** SQL **

So to give an example; from an ASP .NET page on the internet, I would like to populate a DataGrid with the contents of a single table from a SQL database. The SQL database is sitting on our company network behind a firewall.

Could someone please explain / point me in the right direction in how the ASP .NET page / Web Server can securely access the SQL database.

Thanking you in advance
ScottThe firewall needs to allow SQL access between the WebServer and the SQL server. This is usually port 1433. Only requests initiated by the webservers IP should be allowed to get to the db server.

You could also put together some web services on SQL server to send back datasets through the firewall (assuming its running iis and the .net framework).

Then you would just need to open port 80 between the webserver and the sql server. The webservices we run on our db server required an encrypted key as part of all method calls to insure that only we can run them.

No matter which way you do it, your opening a hole in your firewall for communication to occurr between the two boxes.|||Mbanavige – thanks for your reply. Can I confirm my understanding??

Option 1.
The firewall opens port 1433 with an additional rule to only allow the IP address of the web server to communicate on port 1433. As such this allows web server code to create a connection string to SQL? How would I fully qualifying the connection string server property - <SQL Server NetBIOS Name><domain>??

Option 2.
Have a second web server (internal) running on the same box as the SQL server. The internal web server will host web services, which can be called from the external web server. This requires the opening of port 80. Further security is added with the use of an encrypted key required for all method calls. How is the encrypted key implemented?

Is there an industry preferred solution?

Is option 2 more secure against SQL attacks since SQL access is further controlled through the use of web services?

Thanking you again in advance for your replies.
Scott

Design For Loading Data Without Knowlged of Datatype or Column Count

I am loading data from an external source into SQL Server via ASP.NET/C#. The problem is that I do not necessarily know the data types of each column coming in, perhaps until a user tells the application, which might not occur until after the data is loaded. Also, I cannot anticipate the number of columns coming in. What would table design look like?

Would you use a large table with enough columns (e.g. Column1, Column2, etc.) reasonable enough to accomodate all the columns that the source might have (32?), and use nchar as the datatype with the plan to convert/cast when I use the data? Isn't the cast kind of expensive?

Does this make sense? Surely other foplks have run into this...

My thanks!

If you're doing what I think you're doing, you might want to use an EAV (Entity/Attribute/Value) table to store your data. The attribute table stores the column definitions. The entity table store the "row id"s. The value table stores the actual values and is of the form

Entity_id int

Attribute_id int

Value (string/whatever)

You can have multiple value tables for different data types, but I find that it's more trouble than it's worth. I've used this on several projects where the data being captured is set by the users at run time.

Design Advice

HI there my first post so be gentle...

I am redeveloping a web store which is an ASP based site and am looking to make life easier for myself and other staff at the company I work for as well as our customers. I have some problems with my existing data, it is becoming quite a task to manage and this boils down to receiving product information from Vendors in varying formats and in some cases partial data from two different vendors to make one catalogue.

When the site was originally developed it was designed for one "master" product table. We currently still use this and use catalogue numbers and barcodes as our keys to pulling the various supporting information out of our Vendor databases this works OK but we now have many duplicates but fixing the table is not an option as too much of the internal systems rely on this master table for looking up product information.

I have a plan which I am currently in the middle of conceiving, and hence asking for advice here, to re-code our website to directly browse and search purely on the Vendor databases, this way our customers can view the absolute best up to date catalogues and if we need to we can perform a complete reload of the data if we wish most importantly not affecting the other databases or our internal systems. To keep track of what the customers order I intend to add the important product information - such as price, supplier etc to an orders table. Our staff can then use this information to process the orders regardless of the status of my product data that is web facing. Now strictly speaking I dont think this design is correct, as I will be duplicating quite a bit of data into that already exists in my Vendor databases into my orders table and certain problems will arise that I can forsee already like having to write specific code for the browsing and searching of each Vendor database but I think the overall benefits outweigh the current setup. we use SQL server 2k and have several million rows of existing data that will need importing into the new structure aswell. What do you think of doing this i.e. keeping the product data and order data totally seperate and developing the website around this so essentially the add to shopping cart button on the website is what does the adding of data between the two.

phew long post...any advice appreciated!Are these vendor databases you are referring to - are they part of your system or are they the actual remote databases of your vendors ? And how does the "master" relate to these ?|||They make up the product range that we sell from the website and are supplied by the vendors as databases (normally dbf or csv format - but they will usually supply a schema). I will take these databases and provide facility to browse, search and order the products on the website.

As the rest of our system is designed to access a single table for the products, my code on the website takes the information from the Vendor databases and as the customers browse our site & add products to their cart some background code adds records into the master product table so that we can keep track of what they have in their cart.

This solution is not particularly suitable as it creates a lot of duplicate entries (if two different customers order the same product for example or our staff may delete a record for whatever reason and add a new record.)

what I am thinking is to eliminate the master table, and seperate my data into two totally different entities - product data and order data. I doubt this is good design but Its the easiest way I can see of simplifying the system.|||So are the customers browsing the "databases" that the vendors supply to you - you don't import this data into a central products table ?

Why not import the vendor's databases into 1 (use can use dts to help you transform the data into a standard if possible)- rather than waiting on a customer to pick - this would be easier to manage and faster ? Next, use the "true master" as a query only - use the unique key from this and use it to populate the order details table for the customer.|||this would be the ideal however the vendor databases are very different in product information, change frequently (daily/weekly change,additions & deletes) the vendor data also contains "related" product information that isn't directly related to orders that would logically not work in a single product table. My "keys" to pull ou t the related data i need would be barcodes and/or catalogue numbers but these are a) not always unique or b) do not match up (so we lose valuable product information that would generate sales)

My Vendor databases could potentially number 5 - 10 and range from single table 50mb files to 10gb relational structures. Getting it all to work off of a single product table with no dupes and allowing staff to make changes aswell as the daily/weekly updates just spins my head.|||To be honest (and I have very minimal information for this recommendation) I would re-examine the entire process and redesign my structure. The problem you will continue to have is "patching" the process to make everything work based on a design that may have worked in the beginning but is beginning to show it flaws. There are several techniques to handle disjointed data sources that you have while still having a centralized repository of data (usually keys that just point to the actual table/databases that you need to retrieve data). Basically, creating an intermediate table to abstract the complexity of the data sources beneath it, while allowing it to be manageable and maintainable.

There are times that you are much better off punting the existing design - this may have an upfront cost that seems too expensive but realize that in a very short time the maintainability will pay for itself - any customization on both the database and software development side will be minimal. Plus adding additional vendors to this design would be seamless.|||RE: To be honest (and I have very minimal information for this recommendation) I would re-examine the entire process and redesign my structure. The problem you will continue to have is "patching" the process to make everything work based on a design that may have worked in the beginning but is beginning to show it flaws. There are several techniques to handle disjointed data sources that you have while still having a centralized repository of data (usually keys that just point to the actual table/databases that you need to retrieve data). Basically, creating an intermediate table to abstract the complexity of the data sources beneath it, while allowing it to be manageable and maintainable.
There are times that you are much better off punting the existing design - this may have an upfront cost that seems too expensive but realize that in a very short time the maintainability will pay for itself - any customization on both the database and software development side will be minimal. Plus adding additional vendors to this design would be seamless.

RE: [I would re-examine the entire process and redesign my structure.]

S1 I agree, and support the sentiment.

S2 Create a sound logical design that addresses the business needs. This is probably the best advice you can take. Since your situation appears large and complex, you may want to implement a sound end result in two or more intermediate stages that may more easily be budgeted, implemented, and adjusted for 'unforseen' issues over a period of few quarters or longer (rather than use a 'Big Bang' cutover strategy).|||Thanks for the replies definitely very useful comments. What I am considering is to totally seperate my product data from my order data and use code on the website to transfer the neccessary data into my order table so the staff can process the customer orders.

vendor data --> sql server --> website --> sql server --> order tables --> order processing

whereas the current setup is along the lines of

product tables <--> sql server <--> website
^
|
order tables

my data changes so frequently that this is the only simple way I can see of approaching the problem.

If i go along the route of having keys in a centralised database pointing to the source of the information I think this would be too much effort to manage and ensure it is kept in order.

Thanks for your help its great to be able to throw some ideas around as I do not currently have a second technical person to discuss this in detail with!

Design a Database for Daily News Network website

Hello..

I want to upgrade my website to asp.net 2.0 and I want to add a Daily news to website.
and 10 to 15 news will be added daily , so after a year we have almost 4000 entry in the database.

For designing this DB what is your advice on how to store news in database?
Is it better to create same tables for each year ? for example
tblNews_2005
tblNews_2006

Or just to make one table and have all news stored in that ? its gonna be huge after years , isnt that make any problems?

Thanks
Regards.

Nah, just use a simple table... SQL server is highly optimized for large tables. I suppose, however, that you will have a primary key there. Moreover, you should add good indexes in the table

|||

Thanks, I wonder , how large the table could be ? I have an archive of 40,000 records until now and it will be bigger every year.. Regards|||

I've seen sql server responding with the speed of light to one million records

And I've seen it responding poorly in 5000 rows (somebody had forgotten to use indexes...)

It's all to good database design, my friend!

|||

Thanks, so the size doesnt matter :D

The point is in a good design :)