cipher

hi
i'm a web application developer. i am writeing a commercial system. i want to cipher my data. i use microsoft sql server 2000 to store my data.i use ASP to develop my application.
please tell me how i cipher my database and ensure my application security.
thanks.if you're using ASP you're on the wrong site. this is an ASP.NET site. However...

as for 'cipher'ing your data, there's more to security than just ROT13ing the stuff you store in your database. you ought to sit down and read up on web application security rather than just asuming encipherment is your friend (it's not - by definition encipherment is NOT the same as encryption and is inherently breakable)

for a start-out, try www.aspin.com (they have a security section), www.badwebmasters.net, www.securityfocus.com, www.4guysfromrolla.com, www.aspfaq.com, www.developersdex.com and most importantly google. with the right keywords you'll turn up a host of information on ways to secure your ASP code.

j

Child packages : Failed to acquire connection

Hi!

I am having a problem with a Parent package that invokes Child SSIS packages.

The Child packages have EncryptSensitiveWithPassword as their security setting.

I have placed the PackagePassword for each Child in the Parent package.

Each Child package contains a SQL Server Authentication username and password; they are not using Windows Authentication for the SQL Server login.

Here are what seem to be the relevant entries from a log file when the failure occurs:

Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDBInitialize::Initialize failed'. The external request has completed.
OnError,XXXX,YYYYY\xxxx,SQLTask1 in ChildPackage1,GUID3,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

OnError,XXXX,YYYYY\xxxx,ChildPackage1,GUID4,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

When I run the Child package by itself, it works without any errors. So the problem seems to be related to the fact that it is being called by the parent, and somehow the "security settings" for the parent are not quite the same as the settings when I run the Child package by itself.

What might I be doing wrong?

I am using File System storage for my packages, on a shared network drive.

Just to check - you are setting the password in the Execute Package task, right? My apologies if this is obvious, just wanted to confirm. |||

Yes, I have set the password in each Execute Package task -- and the error message is not about an inability to open the package (for which there appear to be a number of error messages available). Instead, it is one that seems to indicate an inability to login to SQL Server with the SQL Server Authentication incorporated in the package.

I have read some web pages (e.g., the link below) that refer to the Integrated Security of a SQL Server connection. I see what appears to be some XML for that in the Child packages (but none in the Parent package) -- and the value that I found was "True". I tried changing it to "SSPI", but then I couldn't even run the child package by itself, nor when called by the Parent.

http://blogs.msdn.com/suryaj/archive/2006/05/09/594039.aspx

|||

Is it possible to use the Debugger in Visual Studio 2005 to study the connection parameter values that are present when a Parent package invokes a Child package?

Dan

|||

I'm going to take a look at using the script Jamie Thompon wrote, as found at

http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

I hope it will give me some clues as to why the connection cannot be acquired when the SSIS package is run as a child package.

|||

John,

Using Jamie Thomson's "script task" I could see that you were correct: my problem was still with the package password. I must have typed it incorrectly, or something.

I am sorry to have dismissed your suggestion so quickly -- but I didn't see it until I was off on vacation and happened to logon and notice your post. So I didn't have SQL Server at my disposal to investigate further. You have my sincere apologies.

Dan

|||No problem - I'm glad you were able to resolve it.

checkbox - using in update statement as bit field

hi
I have a bit field in sql server represented by a checkbox ...
I am updating the database in code ( ie not using formview generated update .. )
the line that is falling over is
.Parameters.Add("@.archive", SqlDbType.Bit).Value = txtarchive.Checked
it falls over saying
failed to convert string parameter to boolean
thevalue of txtarchive.checked is either true or false - how do i convertthis to 1 or 0 or something that sql is happy with ...
thanks

you can use IIF. Give this a try:

IIF(txtarchive.Checked = TRUE, 1,0)

|||

NDinakar's solution should work, but I'm confused as to the problem since I have pretty much the same thing you did, and it works fine.

The only difference is how the parameter was set up, I not only told it that it was a bit, but also a size of 1. (Actually, the sql command/parameters were set up by using a designer, and I copied the code)

check value in table upper case or lower

hi

i want to select * from table1 where name =petter?

now if there is many type of petter in table linke PETTER ,Petter Andpetter which record will come in display?

if i want all this three (PETTER,Petter,petter) will come in display which command is for this ?

regard

It depends on the column collation(default the same as database) setting, which you can check usingsyscolumns. If you want to ignore case, you can change all data into UPPER (or LOWER) case:

DECLARE @.n1 varchar(20)
SELECT @.n1='petter'
SELECT * FROM testCol where UPPER(name)=UPPER(@.n1)

|||

Or you can specify the collation used in the SELECT command:

DECLARE @.n1 varchar(20)
SELECT @.n1='petter'
SELECT * FROM testCol wherename=@.n1
collate SQL_Latin1_General_CP1_CI_AS

You can get descriptions of SQL collations using this statement:

SELECT * FROM ::fn_helpcollations()

|||

that was a good solution

but what it does

collate SQL_Latin1_General_CP1_CI_AS

any ideas

|||

This is used to specify collation used in the SELECT command. I choose SQL_Latin1_General_CP1_CI_AS because this collation is case insensitive (notice the CI). For more information, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5ell.asp

Check the size of individual table in the database

Hi

I am trying to check the size of each table in my database?

SELECT <TableName> , 'Size in bytes/megabytes' FROM DATABASE

I can't for the lif of me figure out how this is done.

I Know if you execute

EXEC sp_HelpDB you get the size of the database, but i want each individual tables size

Any help would be greatly appreciated

Kind Regards

Carel Greaves

Check the responses to your identical question in the Database Engine forum here.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

|||

If you are not opposed to using undocumented features, you can try:

Code Snippet

exec sp_MSforeachtable 'exec sp_spaceused ''?'''

|||

Soz Arnie, I'm still Learning, won't do it again.

But while i'm on the topic.

get an error message when i execute the statement. Although the results are exactly what i want.

And when i send the results to a file it only gives me the tables names, not the stats.

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

|||I don't see a place where you can change that setting; you might try switching your results to text instead of the grid.|||See your other posting for my response.

Check the size of all tables in my database

Hi

I am trying to check the size of each table in my database?

SELECT <TableName> , 'Size in bytes/megabytes' FROM DATABASE

I can't for the lif of me figure out how this is done.

Any help would be greatly appreciated

Kind Regards

Carel Greaves

Courtesy of Vinod Kumar

http://www.extremeexperts.com/SQL/Scripts/FindSizeOfTable.aspx|||

Hi Carel,

in management studio click on database, then right click, then click on reports - > standard reports -> Disk usage by table.

There are quite a few useful reports there....and in case you have not heard of them, there are a set of performance reports which we have found very useful now from MSFT. They can show you things like the plans for sql in process on the machine.....so if you have a slow running query you can look at the machine and then look at the plan that is being used for the running of the query....

This set of performance reports is a good beginning for a set of tools to monitor a server.....well, good for free....if you want better you should look into things like quest.....

Best Regards

Peter

|||

I must be blind because i don't see the reports link, maybe its a plug-in that i don't have with my management studio.

The database is SQL 2000, that is why i am looking for a query to perform the task, however i do connect to this specific server using a SQL Server 2005 Management Console.

|||

Try this:

-- will result in the dump information on space occupied by each table.

EXECsp_msforeachtable'sp_spaceused "?"'

|||

Thats EXACTLY what i want thanks, but it give me this error

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

When i take the results to a file or report then it doesn't include the stats, it only has the table names.

I'm qute new to this, soz guys. I really appreciate the help.

|||If you still have Query Analyzer available, you can run that query there. It does not have the 100 resultset limit that SSMS has.|||

Here is a stored procedure that I use, it doesn't have the resultset display limit, and it does not depend upon 'undocumented' functionality. (We keep getting MSFT folks warning us that it may change in the future...)

Code Snippet

CREATE PROCEDURE dbo.TableSpace
AS
BEGIN

DECLARE
@.TotalRows int,
@.Counter int,
@.TableName varchar(50)

DECLARE @.MyTables table
( RowID int IDENTITY,
TableName varchar(50),
Rows bigint,
Reserved varchar(12),
Data varchar(12),
IndexSize varchar(12),
Unused varchar(12)
)

INSERT INTO @.MyTables ( TableName )
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

SELECT
@.TotalRows = @.@.ROWCOUNT,
@.Counter = 1

WHILE ( @.Counter <= @.TotalRows )

BEGIN

SELECT @.TableName = TableName
FROM @.MyTables
WHERE RowID = @.Counter

INSERT INTO @.MyTables
EXECUTE sp_spaceused @.TableName

SET @.Counter = ( @.Counter + 1 )

END

DELETE FROM @.MyTables
WHERE RowID <= @.TotalRows

SELECT
TableName,
Rows,
Reserved,
Data,
IndexSize,
Unused
FROM @.MyTables
ORDER BY TableName

END
GO

EXECUTE dbo.TableSpace

|||select page_count * 8 * 1024 from sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, 'DETAILED')

This gives you the size for each table, each index, and each partition. The dmv returns the number of pages, which you need to multiple by 8K to get the byte size.

If you only want the information for a certain table / index / partition, you can specify the second, third and fourth parameter. If you specify NULL, you get all tables / indexes / partitions

Thanks,|||

Similar to the stored proc another poster provided, this script will get the info you are looking for and is compatilble with SQL 2000. Of course, you can adjust the results query as you need to fit your uses and provide different statistics. Hope this helps.

if object_id('tempdb..#TableUsage') is not null drop table #TableUsage

create table #TableUsage

(

TableName sysname,

Rows int,

Reserved varchar(20),

ReservedValue as cast(replace(Reserved, ' KB', '') as int),

Data varchar(20),

DataValue as cast(replace(Data, ' KB', '') as int),

Indexsize varchar(20),

IndexsizeValue as cast(replace(Indexsize, ' KB', '') as int),

Unused varchar(20),

UnusedValue as cast(replace(Unused, ' KB', '') as int),

)

exec sp_msforeachtable 'insert #TableUsage ( TableName, Rows, Reserved, Data, Indexsize, Unused ) exec sp_spaceused ''?'''

select

count(*) as Tables,

sum(ReservedValue) as Reserved,

sum(DataValue) as Data,

sum(IndexsizeValue) as Indexsize,

sum(UnusedValue) as Unused

from #TableUsage

if object_id('tempdb..#TableUsage') is not null drop table #TableUsage

Check Table Values in the Store Procedure...

HI

I have a problem related Store Procedure, that i am trying to extact a value from Database (Like FirstName,LastName,Email Address) through Store Procedure and Display it in the DropDownList(Like: FirstName LastName ,(xyz@.xyz.com)) , and this is working correctly.

Now i try to check the value at the same time if it is NULL value in the Database then pass EmptyString to the DropDownList Like ("" "" ,(xyz@.xyz.com))\

how i can do that in the store procedure.

Comments will be appreciated.

Use the IsNull function.

|||

You could save the results to a temp table in the stored procedure then do an update replacing all nulls with "" then just return the contents of the temp table. e.g

CREATE TABLE #tmpTable
(
field1as NVARCHAR(200),
field2as integer
)

INSERT INTO #tmpTable (field1,Field2)
SELECT * FROM SelectionTable

UPDATE #tmpTable SET field1 ="" WHERE field1 isnull

SELECT * FROM #TmpTable

DROP #tmpTable

|||

You can use the Isnull function directly in your select query like this:

select firstName , lastName ,IsNull ( email ,'' )as emailfrom <table Name>

This way you are rest assured that for whichever row the email is null, it will automatically be converted to '' ( blank string ). You can write anything like 'Not Available' in the replacement part of the isnull function.

Hope this will help.