Re: Export xls from sql - Database Discussion Boards

Fad B29-Dec-05 6:50

29-Dec-05 6:50

Hello
MS SQL Server do not have database security, if we stop the service and copy the database file to other computer we can see the schema & data without any password !
So is there is any method for securing the data in SQL Server ?
Thanks for any help Rose | [Rose]

Re: SQL Server security

Colin Angus Mackay29-Dec-05 15:38

Colin Angus Mackay

29-Dec-05 15:38

Fad B wrote:
MS SQL Server do not have database security,

Actually, it has quite a lot of security.

Fad B wrote:
if we stop the service and copy the database file to other computer we can see the schema & data without any password

That is due to your misunderstanding of how it works, not a lack of security on the part of SQL Server. If you stop the service that controls the databases there isn't much SQL Server can do to secure your data.

Fad B wrote:
So is there is any method for securing the data in SQL Server

You need to put in place a comprehensive policy of who gets access to the filesystem on the server so that they cannot move files around arbirtrarily. This also applies to backups. Who gets the physical media and where do they go has to be part of a comprehensive security policy.

You cannot place all of your security in one location. It is a multifaceted problem that must be tackled on all levels. You have seen that by stopping the SQL Server Service (something that won't happen in normal operation) you can get at the files, move them and attach them thus bypassing the inbuilt security in SQL Server. So, you must therefor secure your file system.

There is no single button that you can press on any computer system that will make it secure. As there is no way that it can be done. If you over secure a system then people who have a genuine need cannot access the information they need, if you undersecure it then people who shouldn't have access may be able to gain access. What access is permitted and what is not is determined by the business that it runs in.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius

Re: SQL Server security

Paul Conrad29-Dec-05 18:25

Paul Conrad

29-Dec-05 18:25

Colin Angus Mackay wrote:
You need to put in place a comprehensive policy of who gets access to the filesystem on the server so that they cannot move files around arbirtrarily. This also applies to backups. Who gets the physical media and where do they go has to be part of a comprehensive security policy.

You cannot place all of your security in one location. It is a multifaceted problem that must be tackled on all levels. You have seen that by stopping the SQL Server Service (something that won't happen in normal operation) you can get at the files, move them and attach them thus bypassing the inbuilt security in SQL Server. So, you must therefor secure your file system.

There is no single button that you can press on any computer system that will make it secure. As there is no way that it can be done. If you over secure a system then people who have a genuine need cannot access the information they need, if you undersecure it then people who shouldn't have access may be able to gain access. What access is permitted and what is not is determined by the business that it runs in.

Excellent points made. Security is in the eye of the beholder.

PC

Re: SQL Server security

Fad B29-Dec-05 21:14

Fad B

29-Dec-05 21:14

Ok,
But I did not mean that,
I have SQL Desctop Engin & I'm installing it on any PC
I need to secure my data schema ! and my data !
Since I put them on install CD... so any one will install the database and get its desin...
So any solution to encrypt the database file without the help of the OS ?
Thanks

Re: SQL Server security

Fad B29-Dec-05 21:15

Fad B

29-Dec-05 21:15

Parsing Text file w/ multple datasets and schema.ini?

Joshua Lunsford29-Dec-05 6:33

Joshua Lunsford

29-Dec-05 6:33

so i've been collecting text files with data separated with ';'
I have been using a schema.ini file to define each text file...

example:
schema.ini:

[text.txt]
Format=Delimited(\;\)
ColNameHeader=False
MaxScanRows=0
Character=OEM
Col1=File Text
Col2=problem Text
Col3=serial Text
Col4=name Text
Col5=cube Text
Col6=time Text

data in text.txt:

ASSET;S/N-BIOS;XXXXXX;DPCSXXX;1KXXX;11/21/2005 5:57:43 AM

now, the data i'm going to be receiving in the the near future has different kinds of data separated by blocks all in the same text file. for example

data in text.txt:

[AssetData]
ASSET;S/N-BIOS;XXXXXX;DPCSXXX;1KXXX;11/21/2005 5:57:43 AM

[UserData]
XXXX;XX;XX;XX

How can I parse this data(previously using a Oledb connection with the schema.ini) into individual datasets?

Thanks alot in advanced

-- modified at 12:34 Thursday 29th December, 2005

Export xls from sql

GoodQuestion28-Dec-05 6:52

GoodQuestion

28-Dec-05 6:52

I am having trouble to get export from sql. I can get import working well. When I am importing xls to sql and it was working fine. when I am exporting to new file and I tried to open xls file and it said it cannot open file bec of poor data. I have no idea how can I fix this one. I am hoping that yo can help this one.
_BulkCopyPtr spIBulkCopy;
HRESULT hr = S_OK;
spIBulkCopy.CreateInstance(__uuidof(BulkCopy));
CString strFile;

m_EditTextBox.GetWindowText(strFile);

spIBulkCopy->DataFilePath = strFile.AllocSysString();
spIBulkCopy->DataFileType = SQLDMODataFile_TabDelimitedChar;
//spIBulkCopy->DataFileType = SQLDMODataFile_NativeFormat;
spIBulkCopy->ErrorFilePath = "c:\errores.log";
spIBulkCopy->ServerBCPDataFileType = SQLDMOBCPDataFile_Native;
spIBulkCopy->UseBulkCopyOption = true;
//spIBulkCopy->UseExistingConnection = true;
spIBulkCopy->IncludeIdentityValues = true;

m_ctrlDBTable.GetWindowText(strFile);
_bstr_t bstrFile(strFile);
int nCount = m_spITables->Count;
for (int i=0; i<ncount; i++)
="" {
="" m_spitable="m_spITables-">Item(i + 1, vtMissing);
if(m_spITable->GetName().operator==( bstrFile))
break;

m_spITable = NULL;
}

hr=m_spITable->ExportData(spIBulkCopy);//it worked with importData

Re: Export xls from sql

S Douglas5-Jan-06 0:31

S Douglas

5-Jan-06 0:31

GoodQuestion wrote:
Export xls from sql

The easiest way to export data from a database to an excel file is through an Select into querry. I know this works on an Access db, never thought of trying it on an SQL server but it should still work (using ADO).

CString str;
str.Format("SELECT * INTO [Excel 8.0;DATABASE=%s].[%s] FROM [%s]", ExcelFile, Worksheet, table);

//pConn is a ADODB Connection
pConn->Execute(str.AllocSysString(), NULL, adExecuteNoRecords);

ZeePain! wrote:
This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.

thedailywtf.com[^]

-- modified at 6:31 Thursday 5th January, 2006

Re: Export xls from sql

GoodQuestion11-Jan-06 12:02

GoodQuestion

11-Jan-06 12:02

Thanks for reply my question. That means to me alot. Sadly, I cant get it working bec of DB_E_ERRORSINCOMMAND stuff. I dont understand what is DB_E_ERRORSINCOMMAND for? But I know error is related to spIConnection->Execute bec that is where error came from. can you please explain what is its cause? THANK YOU!!

_bstr_t bstrConnect( L"driver={sql server};"
L"server=COYDELL;"
L"Database=brntbl;UID=sa;PWD=;" );

_ConnectionPtr spIConnection;
spIConnection.CreateInstance( __uuidof(Connection ) );
spIConnection->ConnectionString = bstrConnect;
spIConnection->Open("DSN=BurnDB","","", NULL);

CString bstrCommand;
bstrCommand.Format("SELECT * INTO [Excel 8.0;DATABASE=C:\\exceltest.xls].[HoleDiameters$] FROM [HoleDiameters]");

HRESULT hr = spIConnection->Execute(bstrCommand.AllocSysString(),NULL,adExecuteNoRecords);
spIConnection->Close();

"HoleDiameters" is name of sheet and Table from DB

SQL table organization for authentication

shapper28-Dec-05 3:48

shapper

28-Dec-05 3:48

Hello,

I have a similar post in Asp.Net forum. My questions are related with Asp.Net 2.0 and SQL 2005 so I am not sure if this shouldn't be the best forum to post this message.

I am working on an Asp.Net 2.0 / MSSQL 2005 web site with authentication.

My web site has two type of users: "students" and "professors". They are not related!
All users need to login. Students use the role "student" and professors use the role "professor".

The information for students is different from the information of professors.
I could put all students and professors in the same table and leave some fields blank.

My option was to create the following tables:
A) "Authentication" with the fields:
[UserId], [Password], [PasswordFormat], [MobilePIN], [Email], [PasswordQuestion],
[PasswordAnswer], [IsApproved], [CreateDate], [LastLoginID] and [UserRole].

B) "Students" with the field [StudentID] and all the information fields for students.

C) "Professors" with the field [ProfessorID] and all the information fields for professors.

My questions are:
1. Am I following the right approach by using these 3 tables?
2. Should I place all info from users (students, professors and authentication) in "Authentication" table?
3. Should I keep only two tables, "Students" and "Professors" and move the login information to each record in these tables?
4. If using the 3 tables how can I know the info for a user after it logins? I could use a foreign key in Authentication table but I have 2 tables: students and professors.

Anyway this is really confusing.

Can someone, please, give me some advice of how to create the tables and set this authentication?

Thanks,
Miguel

Re: SQL table organization for authentication

Paul Conrad28-Dec-05 6:38

Paul Conrad

28-Dec-05 6:38

Miguel,

Your approach is good, and placing all user info in one table is okay. If you were to use two tables, then you'd probably need separate login pages for students and professors. For the last question, I would keep track of the session (or a cookie, this would require cookies to be enabled on the one hand) and tie it to the user, maybe in the authentication table. I do not know much about asp.net but this is how I would do it with a database and php. Seems like you are on track and I hope this helps some.

Paul

Re: SQL table organization for authentication

Paul Conrad28-Dec-05 6:51

Paul Conrad

28-Dec-05 6:51

If you were to go with a session id, you'd need a table holding the session id and the user associated to the session id. All you'd have to do is pass the session id from page to page.

Paul

how to declare the procedures

sasire1828-Dec-05 0:53

sasire18

28-Dec-05 0:53

hi i have the following code
the code

CREATE PROCEDURE proc_InsertOrder
(@OrderId int output,
 @CustomerName varchar(50),
 @ShippingAddress varchar(50)
)
 AS

INSERT INTO Orders (CustomerName, ShippingAddress)
VALUES
(@CustomerName, @ShippingAddress)

SELECT @OrderId=@@IDENTITY

when i try to execute it asks for declaration of the OrderID if i declare then it asks for declaration of CustomerName now i dont know how to deal with this situation can anyone tell what i can do.

Procedure 'proc_InsertOrder' expects parameter '@OrderId', which was not supplied.

or is there any proper code for the decalaration

thanks

sasire18

Re: how to declare the procedures

S. Akif Kamal28-Dec-05 1:24

S. Akif Kamal

28-Dec-05 1:24

It depends on how you are executing the procedure. As per my knowledge the correct way of executing the procedure is :

Declare @OrderID int

exec proc_InsertOrder @OrderId output, 'Akif', 'Test'

Akif

storing xml doc in asp page uisng sqlserver

suneelbabu_net27-Dec-05 19:02

suneelbabu_net

27-Dec-05 19:02

write an asp page that actually creates an xml document

And in that page, after the xml document is created, the stored procedure should be called and the xml document should be passed as the input parameter

suneel

storing xml file in sqlserver

suneelbabu_net27-Dec-05 15:59

suneelbabu_net

27-Dec-05 15:59

I want to create an xml document and want to store in sqlserver.
And i want to create xml document in asp page and want to store in database as input parameter using stored procedure.
Can any body help to me
please send to suneel@softwarenucleus.com

suneel

Re: storing xml file in sqlserver

rihdus27-Dec-05 18:19

rihdus

27-Dec-05 18:19

Can you tell me urgency to store whole file in sql server database.
Instead, u can save url of the file and file physically to some folder in server. Smile | :)

Any systematic work reflects its significance for a long time, Though I m not totally against "The Prototyping".So let's discuss in depth...

Re: storing xml file in sqlserver

suneelbabu_net27-Dec-05 18:42

suneelbabu_net

27-Dec-05 18:42

Iam happy about your reply. thank u.

As i have two stored procedures in my project, so i want to get all details from one stored procedure and i want to send the information as input paramenter to the second stored procedure.

suneel

MS-SQL server Self join

Amit Malik25-Dec-05 19:11

Amit Malik

25-Dec-05 19:11

Hello,
This is urgent.
I have a Table ARTICLE_MASTER.
fields are item_code, activation_date.

I can different item_code for each article.
each article has a activation date.

select * from ARTICLE_MASTER
inner join
ARTICLE_MASTER ARTICLE_MASTER1
on ARTICLE_MASTER.item_code= ARTICLE_MASTER1.item_code
where ARTICLE_MASTER.activation_date < = getdate() and
ARTICLE_MASTER.end_date > = getdate() and
ARTICLE_MASTER.activation_date > ARTICLE_MASTER1.activation_date
order by article_master.item_code

I can have 4 rows for a single article - say 001- for Comb.
and for a given month say December, my acticle master can have 4 rows for article 001. with activation date and end date, for a single article i can have 4 rows.
say

ITEM_CODE               ACTIVATION_DATE            END_DATE        PRICE
001                     01/12/2005                 31/12/2005      15
001                     07/12/2005                 31/12/2005      20
001                     14/12/2005                 31/12/2005      25
001                     21/12/2005                 31/12/2005      20

SO After comparison with my current date i have to decide which price to apply.
say on 2nd dec price should be 15
on 8th dec price should be 20
on 15th dec price should be 25
on 21-22th dec price should be 20
so i need to pick the appropriate row for a given date.
The query i have written above gives only the latest one say on 16th still it gives me 21st dec's row.

Please give me a solution for it.
its urgent

Amit Malik

.NET Developer

Re: MS-SQL server Self join

Michael Potter27-Dec-05 3:22

Michael Potter

27-Dec-05 3:22

It may look something like this:

SELECT
     am1.* 
FROM 
     ARTICLE_MASTER am1
WHERE 
     getdate() BETWEEN am1.activation_date AND am1.end_date AND
     am1.activation_date = (SELECT 
                                 MIN(am2.activation_date)
                            FROM 
                                 ARTICLE_MASTER am2
                            WHERE 
                                 am2.item_code = am1.item_code AND
                                 getdate() BETWEEN am2.activation_date AND am2.end_date)
ORDER BY 
     item_code

delete trigger

Vipin.d25-Dec-05 17:16

Vipin.d

25-Dec-05 17:16

hi ,
i am new in mssql2000.
i have table named journal. before inserting dat into it , check that journal no. exist, if exist delete that journal and insert the new one.

is it do with trigger? then how?

with regards
vipin Smile | :)

vipin

Re: delete trigger

Colin Angus Mackay26-Dec-05 2:40

Colin Angus Mackay

26-Dec-05 2:40

You could do this with a trigger. However, why delete the row? Why not update the existing row with the new data.

Personally, I do this with a stored procedure.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius

need help about database

ngAnhTuan24-Dec-05 20:54

ngAnhTuan

24-Dec-05 20:54

hello everybody
i'm Tuan, i have just stated with database(SQL server 2000).i hardly knwon anything about database,if anyone who have a small program about database , please send to hepl me to do practice :How to creat database, add in with VB.net with component connection!
Thanks bro!!! Smile | :)

send to me at: anhtuantelecom@yahoo.com
-- modified at 2:54 Sunday 25th December, 2005

Re: need help about database

Colin Angus Mackay26-Dec-05 2:37

Colin Angus Mackay

26-Dec-05 2:37

I would suggest that your best course of action would be to get a book on ADO.NET and one SQL Server 2000.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius

Re: need help about database

Paul Conrad28-Dec-05 5:34

Paul Conrad

28-Dec-05 5:34

Or take a look around at Code Project. There are numerous articles that can help.

Last Visit: 31-Dec-99 18:00 Last Update: 25-Jun-24 22:39

Refresh

ᐊ Prev 1...1989 1990 1991 199219931994 1995 1996 1997 1998 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.