Creation of credentials and certificates to protect a DB

Hi, i want to know if its posible to create credentials or certificates in order to protect a SQL 2005 data base.

Because if someone Buckups one of my DBs from my server, and try to restore it in orther server i dont want they to see my DB information because he dont have the correct credentials or certificates for it.

This is posible?. if is, How i do it ?

Best Regards.

You can use encryption, so that if a database is stolen, the thief cannot retrieve the original data. For more information, please consult the resources mentioned in the second post of the following thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286374&SiteID=1.

Also, note that if you are trying to protect against a machine administrator or a sysadmin, then encryption may not be sufficient. You can check the following thread for this aspect: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371562&SiteID=1.

Thanks
Laurentiu

|||

I dont know if i can encrypt al the DB, because the information would not be retrived correct to the front end, and Encrypting/decrypting all the time the DB would take lots of time.

Regards.

|||

You don't need to encrypt the entire database, you just need to encrypt the information in it that you are trying to protect.

If you don't want to rely on encryption, then the only secure solution is to control access to the database files by limiting access to them and by controlling the access that you grant in SQL Server.

Thanks
Laurentiu

|||Unfortunately, that particular feature doesn't currently exist. You still need to apply physical security to your backups. It's a feature that I'd like to see in the next version of SQL Server though. Right now, the best that you can do is to encrypt data which can not be decrypted, even after restoring on another instance of SQL Server unless they have the correct database key loaded and then the correct certificate (if you're using certificate based encryption).

Creation of credentials and certificates to protect a DB

Hi, i want to know if its posible to create credentials or certificates in order to protect a SQL 2005 data base.

Because if someone Buckups one of my DBs from my server, and try to restore it in orther server i dont want they to see my DB information because he dont have the correct credentials or certificates for it.

This is posible?. if is, How i do it ?

Best Regards.

You can use encryption, so that if a database is stolen, the thief cannot retrieve the original data. For more information, please consult the resources mentioned in the second post of the following thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286374&SiteID=1.

Also, note that if you are trying to protect against a machine administrator or a sysadmin, then encryption may not be sufficient. You can check the following thread for this aspect: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371562&SiteID=1.

Thanks
Laurentiu

|||

I dont know if i can encrypt al the DB, because the information would not be retrived correct to the front end, and Encrypting/decrypting all the time the DB would take lots of time.

Regards.

|||

You don't need to encrypt the entire database, you just need to encrypt the information in it that you are trying to protect.

If you don't want to rely on encryption, then the only secure solution is to control access to the database files by limiting access to them and by controlling the access that you grant in SQL Server.

Thanks
Laurentiu

|||Unfortunately, that particular feature doesn't currently exist. You still need to apply physical security to your backups. It's a feature that I'd like to see in the next version of SQL Server though. Right now, the best that you can do is to encrypt data which can not be decrypted, even after restoring on another instance of SQL Server unless they have the correct database key loaded and then the correct certificate (if you're using certificate based encryption).

Creating specific users

I have a user who is just responsible for monitoring SQL backups. However I
seem to have to grant sys admin rights in order for the user to be able to
see the backups in Enterprise manager. What is the lowest level that I can
grant to the user in this situation ?
SiSimon
Why do the users need to see the backups in EM? The files are located on
filesystem.
Can just users issue BACKUP DATABASE/RESTORE DATABASE... from QA?
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:1A221F70-5B21-4A76-97DF-4BF62A4E4196@.microsoft.com...
>I have a user who is just responsible for monitoring SQL backups. However I
> seem to have to grant sys admin rights in order for the user to be able to
> see the backups in Enterprise manager. What is the lowest level that I can
> grant to the user in this situation ?
> Si|||The whole backup regime was setup before I joined the company. They use SQL
jobs to run the SQL backups onto tape. A user has been designated to check E
M
to ensure the tape contents are correct. This will change in the medium term
as we are looking at how everything is backed up and will probably move to
something like Quest Litespeed. However in the short term I don`t really wan
t
to change the way things are done, only to change in the future.
"Uri Dimant" wrote:

> Simon
> Why do the users need to see the backups in EM? The files are located on
> filesystem.
> Can just users issue BACKUP DATABASE/RESTORE DATABASE... from QA?
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:1A221F70-5B21-4A76-97DF-4BF62A4E4196@.microsoft.com...
>
>|||Simon,
A user does not need to be sysadmin to run backups (such as through Query
Analyze as Uri mentioned), but I believe that Enterprise Manager for SQL
Server 2000 specifically checks for sysadmin in the application.
So, as long as SQL Server 2000 and Enterprise Manager are the tools, I think
you are stuck with this level of access. Perhaps your revised approach will
not be too far away.
RLF
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:26A07029-860D-40FB-B382-B064819B3A04@.microsoft.com...[vbcol=seagreen]
> The whole backup regime was setup before I joined the company. They use
> SQL
> jobs to run the SQL backups onto tape. A user has been designated to check
> EM
> to ensure the tape contents are correct. This will change in the medium
> term
> as we are looking at how everything is backed up and will probably move to
> something like Quest Litespeed. However in the short term I don`t really
> want
> to change the way things are done, only to change in the future.
> "Uri Dimant" wrote:
>|||Hi,
The minimum rights a user account needs is:
Public (can not remove)
db_backupoperator
db_denydatareader (don't need but locks down)
db_denydatawriter (don't need but locks down)
If db_denydatareader is not set the user will be able to see the tables and
the structure (table properties only), but not the data in the tables. See
the stored procedures and views there content but cannot edit them. When
selected it will hide all the tables, views, stored procedures and users
from them and they will only be able to backup the database.
The only issue you will have is if they are backing up to disk they will not
be able to browse the DIR structure but can enter the location manually.
Alternatively you can set it up as a backup device.
- Mike
----
--
<a href="http://links.10026.com/?link=http://www.cogitar.net"> Cogitar Software. (
http://www.cogitar.net ) </a>
http://www.cogitar.net Cogitar Software. (Software Development and Managment
Systems)
http://www.web-dominion.co.uk Web-Dominion. (Web Design and hosting )
http://shop-dominion.com (senery landscape pictur gallery)
----
--
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:1A221F70-5B21-4A76-97DF-4BF62A4E4196@.microsoft.com...
>I have a user who is just responsible for monitoring SQL backups. However I
> seem to have to grant sys admin rights in order for the user to be able to
> see the backups in Enterprise manager. What is the lowest level that I can
> grant to the user in this situation ?
> Si

Creating Reports

What needs to be installed on a workstation in order to be able to create
reports for reporting services?
I have Visual Studio .NET installed, and having installed the reporting
services client noticed it had appeared as an option in VS.
A colleague installed the reporting services client who didn't have Visual
Studio .NET and doesn't seem to have the ability to create a report.You need VS. I've read here about other software to generate the RDL,
but VS makes it much nicer.

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.
Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.
Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.
Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating multilple tables at once

I had a SQL db that i copied all the tables into excel and it generated the quereies in order to create those tables....Is there anyway i could excute all those table creations at once or would it be easier to just write an application that does that for me?i am a little confused on what you are asking. That said if you have generated several CREATE TABLE scripts and your question here is can you compile them all into one big script and execute it to recreate all the tables at once, the answer is YES you can.|||How i do it is I open the Query ananlyzer and input the CREATE TABLE query I see it say the command has excuted succesfully but when does the actual table show up in the SQL server? does it have automatically or do i need to do some eles to actually create the table? How would i go about creating the create table script?|||

since you are using the term "Query Analyzer" I will presume that you are using SQL 2000...

In Query Analyzer you need to display the object explorer. If memory servesI think it was View/Object Explorer but regardless its in the main menu somewhere. Once you have object explorer displayed explore down to an individual table (you may be able to select multiple again its been a while) and select Tasks/Generate Script. Also in Enterprise Mgr. I believe you can perform the equivilent in Enterprise Mgr. (but i seem to recall thoughts that I couldnt so dunno for sure on that). Overall its a VERY EASY PROCESS.

Then open up Query Analyzer, select open file/query, browse to the .sql file you created previously and a new query window with the script's contents will be shown. Hit ctrl & F5 to syntax check it (just a habit of mine personally) and then hit F5 again to execute it. Now via Enterprsie Mgr. and Query Analyzer Object Explorer you should see your new tables. You can interact with the new tables via TSQL Queries or via the GUI in Enterprise Mgr.

Derek