maintenance plan Cleanup Task

Hello all,

What is the latest after SP2 on the Maintenace Cleanup Task?

Has anyone been able to successfully delete old backup files(*.bak) and tran logs (*.trn) TOGETHER using maintenance plan cleanup task in SQL 2005 SP2 with all the htfixes instaled? .

In other words can EXECUTE master.dbo.xp_delete_file 0,N'D:\backups',N'bak',N'2007-07-12T10:12:03',1 be used with bak and trn at the same time

Thanks in advanced

This has been fixed in the latest cumulative update for SQL Server. Contact Customer Support and they will direct you to the fix.

jkh

maintenance plan Cleanup Task

Hello all,

What is the latest after SP2 on the Maintenace Cleanup Task?

Has anyone been able to successfully delete old backup files(*.bak) and tran logs (*.trn) TOGETHER using maintenance plan cleanup task in SQL 2005 SP2 with all the htfixes instaled? .

In other words can EXECUTE master.dbo.xp_delete_file 0,N'D:\backups',N'bak',N'2007-07-12T10:12:03',1 be used with bak and trn at the same time

Thanks in advanced

This has been fixed in the latest cumulative update for SQL Server. Contact Customer Support and they will direct you to the fix.

jkh

Maintaining Security

I am a beginer in SQL Server. I have developed a simple accounting application in VB and SQL. Now I have successfully completed my application. Now I want to deploy it to my client. So I installed SQl Server and required VB components in the clients computer. I also created 'sa' login and secret password only know by me. I thought my data in that clients computer was full safe but later on i found that we can also connect to the sql server using the NT administrative account and easily change the data of the database. So now I am worried that if someone enters and access the clients computer with administrator's password then he/she can change my data resulting the corruption of the data. So is there any way that I can prevent the access the database to the client with the NT administrative account or any way 2 track the way the data changed?

I am probably missing something from your application description; please correct me if any of my assumptions is wrong. I will list a few assumptions based on my understanding and try to answer your question and you some recommendations based on them:

* There is one SQL Server 2005 machine that can be remotely access by multiple clients running your application.

* You have a hardcoded SA password embedded in your application

I want to start by pointing out that by default members of the Windows Administrators group on the machine running SQL Server will have access to via this membership. You can remove such privilege to prevent an accidental access by removing the BUILTIN\Administrators login from SQL Server: DROP LOGIN [BUILTIN\Administrators]

It is important to remark that this will prevent the Windows administrators from connecting to SQL Server 2005 when the server is running normally, but any local administrator will be able to connect to SQL Server 2005 by starting the server in single-user-mode, this is allowed for system maintenance and to prevent accidental lockouts from the system. It is also important to note that it is pretty much impossible to stop an adversary that already has full access to the SQL Server 2005 machine as system administrator.

The next thing to note, and that hopefully is something I misunderstood is the hardcoded SA password in your application. I strongly recommend against such practice as an adversary can easily recover the plaintext password from your client and compromise the data. Even more, the account you are using in this case is SA; I strongly recommend using the “least privilege principle” and define roles for your application, based on the most common tasks. Probably you have administrative tasks (create users, tables, etc.), read-write tasks (update, inserts, etc.) and read-only tasks (search and read data, but not write). I would recommend creating different principals for such tasks and grant only the required privileges for each principal type, restricting access to the most privileged accounts (such as SA) only to users that require such high privileges.

Additionally I want to remind you of a new feature in SQL Server 2005 that may affect your design in case you use hardcoded passwords: password policy. SQL Server 2005 running on Windows 2003 can take advantage of the Windows password policies and use them in SQL logins (including SA); these password policies allows a domain/machine administrator to define password complexity, life-time, lockout policies, etc. in a centralized way. When running on Windows 2003, SQL Server will use this new feature by default, but you can explicitly turn it off for any given SQL account. It is highly recommended to take advantage of this feature especially for SA, but if you have hardcoded passwords (or passwords that are difficult to update in your app) you will probably have to disable the password policy for the accounts you use in your app.

Detect unexpected value changes is a little tricky. One possibility here may be to use a certificate to sign the data and store the signature along with the data; this way your application can verify that the signature is valid before consuming the data.

I hope this information will be useful, let us know if you have further questions or/and feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||Thanks. As I am only concern abt the protection of my data I wanna suggestion on "how do I safe gaurd my data" so only the login used by me or my program can change/add/remove the data from my database not other users from any other program. And I also wanna know that whether can I track the activities of third user who changes the data in my database.|||

You might want to take a look at a thread that is related to your question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371562&SiteID=1.

You can only achieve what you are asking if you have full control over the machine where your program is installed. Otherwise, you could write your program, so you could detect if someone changed the data, but you would not be able to prevent such changes. In other words, there is no way to prevent corruption of your data by a third party.

Thanks
Laurentiu

Mail Profile sql 2005 : How to retry until successfully sent?

Hi
I recently had a big problem with an important job, after sending an email
using the msdb..sp_send_dbmail stored procedure the job succeeded!
BUT
the emails never went out! because the email server was problematic at
roughly the same time. This is the error that was logged in the sql 2005
email job log.:
No connection could be made because the target machine avtively refused it.
mail server failure ... bla bla bla
When i reran the job a couple of hours later the mails went out because the
mail server was up and running.
My question - is there not a way to tell the mail profile to retry sending
the email a couple of times with say 1 minute intervals between retries -
same as in a sql 2005 job'
thanks
IanHi
As I understood, you may want to PING the server
to check whether or not the server is running up and if it succeded then
send the email
Some ideas, check it out
SET NOCOUNT ON
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
"I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> Hi
> I recently had a big problem with an important job, after sending an email
> using the msdb..sp_send_dbmail stored procedure the job succeeded!
> BUT
> the emails never went out! because the email server was problematic at
> roughly the same time. This is the error that was logged in the sql 2005
> email job log.:
> No connection could be made because the target machine avtively refused
> it.
> mail server failure ... bla bla bla
> When i reran the job a couple of hours later the mails went out because
> the
> mail server was up and running.
> My question - is there not a way to tell the mail profile to retry sending
> the email a couple of times with say 1 minute intervals between retries -
> same as in a sql 2005 job'
> thanks
> Ian|||You can set the number of retires and the retry delay
interval at the server configuration level. In T-SQL, you
can use sysmail_configure_sp. If you are using the Database
Mail Configuration wizard in Management Studio, select the
option to View or Change System Parameters.
-Sue
On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
Coetzer@.discussions.microsoft.com> wrote:
>Hi
>I recently had a big problem with an important job, after sending an email
>using the msdb..sp_send_dbmail stored procedure the job succeeded!
>BUT
>the emails never went out! because the email server was problematic at
>roughly the same time. This is the error that was logged in the sql 2005
>email job log.:
>No connection could be made because the target machine avtively refused it.
>mail server failure ... bla bla bla
>When i reran the job a couple of hours later the mails went out because the
>mail server was up and running.
>My question - is there not a way to tell the mail profile to retry sending
>the email a couple of times with say 1 minute intervals between retries -
>same as in a sql 2005 job'
>thanks
>Ian|||Hi
This would not have solved the problem because the server was up and running
- but the mail service was hanging / not working i think.
bye
"Uri Dimant" wrote:
> Hi
> As I understood, you may want to PING the server
> to check whether or not the server is running up and if it succeded then
> send the email
> Some ideas, check it out
> SET NOCOUNT ON
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip
>
>
> "I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
> news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> > Hi
> >
> > I recently had a big problem with an important job, after sending an email
> > using the msdb..sp_send_dbmail stored procedure the job succeeded!
> > BUT
> > the emails never went out! because the email server was problematic at
> > roughly the same time. This is the error that was logged in the sql 2005
> > email job log.:
> > No connection could be made because the target machine avtively refused
> > it.
> > mail server failure ... bla bla bla
> >
> > When i reran the job a couple of hours later the mails went out because
> > the
> > mail server was up and running.
> >
> > My question - is there not a way to tell the mail profile to retry sending
> > the email a couple of times with say 1 minute intervals between retries -
> > same as in a sql 2005 job'
> >
> > thanks
> >
> > Ian
>
>|||brilliant! thank you very much - now to wait and see what happens tomorrow
morning ...
bye
"Sue Hoegemeier" wrote:
> You can set the number of retires and the retry delay
> interval at the server configuration level. In T-SQL, you
> can use sysmail_configure_sp. If you are using the Database
> Mail Configuration wizard in Management Studio, select the
> option to View or Change System Parameters.
> -Sue
> On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
> Coetzer@.discussions.microsoft.com> wrote:
> >Hi
> >
> >I recently had a big problem with an important job, after sending an email
> >using the msdb..sp_send_dbmail stored procedure the job succeeded!
> >BUT
> >the emails never went out! because the email server was problematic at
> >roughly the same time. This is the error that was logged in the sql 2005
> >email job log.:
> >No connection could be made because the target machine avtively refused it.
> >mail server failure ... bla bla bla
> >
> >When i reran the job a couple of hours later the mails went out because the
> >mail server was up and running.
> >
> >My question - is there not a way to tell the mail profile to retry sending
> >the email a couple of times with say 1 minute intervals between retries -
> >same as in a sql 2005 job'
> >
> >thanks
> >
> >Ian
>

Mail Profile sql 2005 : How to retry until successfully sent?

Hi
I recently had a big problem with an important job, after sending an email
using the msdb..sp_send_dbmail stored procedure the job succeeded!
BUT
the emails never went out! because the email server was problematic at
roughly the same time. This is the error that was logged in the sql 2005
email job log.:
No connection could be made because the target machine avtively refused it.
mail server failure ... bla bla bla
When i reran the job a couple of hours later the mails went out because the
mail server was up and running.
My question - is there not a way to tell the mail profile to retry sending
the email a couple of times with say 1 minute intervals between retries -
same as in a sql 2005 job?
thanks
Ian
Hi
As I understood, you may want to PING the server
to check whether or not the server is running up and if it succeded then
send the email
Some ideas, check it out
SET NOCOUNT ON
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
"I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> Hi
> I recently had a big problem with an important job, after sending an email
> using the msdb..sp_send_dbmail stored procedure the job succeeded!
> BUT
> the emails never went out! because the email server was problematic at
> roughly the same time. This is the error that was logged in the sql 2005
> email job log.:
> No connection could be made because the target machine avtively refused
> it.
> mail server failure ... bla bla bla
> When i reran the job a couple of hours later the mails went out because
> the
> mail server was up and running.
> My question - is there not a way to tell the mail profile to retry sending
> the email a couple of times with say 1 minute intervals between retries -
> same as in a sql 2005 job?
> thanks
> Ian
|||You can set the number of retires and the retry delay
interval at the server configuration level. In T-SQL, you
can use sysmail_configure_sp. If you are using the Database
Mail Configuration wizard in Management Studio, select the
option to View or Change System Parameters.
-Sue
On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
Coetzer@.discussions.microsoft.com> wrote:

>Hi
>I recently had a big problem with an important job, after sending an email
>using the msdb..sp_send_dbmail stored procedure the job succeeded!
>BUT
>the emails never went out! because the email server was problematic at
>roughly the same time. This is the error that was logged in the sql 2005
>email job log.:
>No connection could be made because the target machine avtively refused it.
>mail server failure ... bla bla bla
>When i reran the job a couple of hours later the mails went out because the
>mail server was up and running.
>My question - is there not a way to tell the mail profile to retry sending
>the email a couple of times with say 1 minute intervals between retries -
>same as in a sql 2005 job?
>thanks
>Ian
|||Hi
This would not have solved the problem because the server was up and running
- but the mail service was hanging / not working i think.
bye
"Uri Dimant" wrote:

> Hi
> As I understood, you may want to PING the server
> to check whether or not the server is running up and if it succeded then
> send the email
> Some ideas, check it out
> SET NOCOUNT ON
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip
>
>
> "I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
> news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
>
>
|||brilliant! thank you very much - now to wait and see what happens tomorrow
morning ...
bye
"Sue Hoegemeier" wrote:

> You can set the number of retires and the retry delay
> interval at the server configuration level. In T-SQL, you
> can use sysmail_configure_sp. If you are using the Database
> Mail Configuration wizard in Management Studio, select the
> option to View or Change System Parameters.
> -Sue
> On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
> Coetzer@.discussions.microsoft.com> wrote:
>
>

Mail Profile sql 2005 : How to retry until successfully sent?

Hi
I recently had a big problem with an important job, after sending an email
using the msdb..sp_send_dbmail stored procedure the job succeeded!
BUT
the emails never went out! because the email server was problematic at
roughly the same time. This is the error that was logged in the sql 2005
email job log.:
No connection could be made because the target machine avtively refused it.
mail server failure ... bla bla bla
When i reran the job a couple of hours later the mails went out because the
mail server was up and running.
My question - is there not a way to tell the mail profile to retry sending
the email a couple of times with say 1 minute intervals between retries -
same as in a sql 2005 job'
thanks
IanHi
As I understood, you may want to PING the server
to check whether or not the server is running up and if it succeded then
send the email
Some ideas, check it out
SET NOCOUNT ON
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
"I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> Hi
> I recently had a big problem with an important job, after sending an email
> using the msdb..sp_send_dbmail stored procedure the job succeeded!
> BUT
> the emails never went out! because the email server was problematic at
> roughly the same time. This is the error that was logged in the sql 2005
> email job log.:
> No connection could be made because the target machine avtively refused
> it.
> mail server failure ... bla bla bla
> When i reran the job a couple of hours later the mails went out because
> the
> mail server was up and running.
> My question - is there not a way to tell the mail profile to retry sending
> the email a couple of times with say 1 minute intervals between retries -
> same as in a sql 2005 job'
> thanks
> Ian|||You can set the number of retires and the retry delay
interval at the server configuration level. In T-SQL, you
can use sysmail_configure_sp. If you are using the Database
Mail Configuration wizard in Management Studio, select the
option to View or Change System Parameters.
-Sue
On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
Coetzer@.discussions.microsoft.com> wrote:

>Hi
>I recently had a big problem with an important job, after sending an email
>using the msdb..sp_send_dbmail stored procedure the job succeeded!
>BUT
>the emails never went out! because the email server was problematic at
>roughly the same time. This is the error that was logged in the sql 2005
>email job log.:
>No connection could be made because the target machine avtively refused it.
>mail server failure ... bla bla bla
>When i reran the job a couple of hours later the mails went out because the
>mail server was up and running.
>My question - is there not a way to tell the mail profile to retry sending
>the email a couple of times with say 1 minute intervals between retries -
>same as in a sql 2005 job'
>thanks
>Ian|||Hi
This would not have solved the problem because the server was up and running
- but the mail service was hanging / not working i think.
bye
"Uri Dimant" wrote:

> Hi
> As I understood, you may want to PING the server
> to check whether or not the server is running up and if it succeded the
n
> send the email
> Some ideas, check it out
> SET NOCOUNT ON
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip
>
>
> "I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
> news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
>
>|||brilliant! thank you very much - now to wait and see what happens tomorrow
morning ...
bye
"Sue Hoegemeier" wrote:

> You can set the number of retires and the retry delay
> interval at the server configuration level. In T-SQL, you
> can use sysmail_configure_sp. If you are using the Database
> Mail Configuration wizard in Management Studio, select the
> option to View or Change System Parameters.
> -Sue
> On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
> Coetzer@.discussions.microsoft.com> wrote:
>
>