SSIS Checkpoint Files Security Threat
Apr 24, 2007Is it possible to store SSIS checkpoint files in database rather than file system?
View 1 Replies
ADVERTISEMENT
SSIS Event Handler And Checkpoint Files
Mar 23, 2007
Has anyone used Checkpoint files in conjunction with the OnError Event Handler ? I'm having a problem getting the OnError event to fire when the SSIS package reruns with the Checkpoint file.
The first run of the package (without a checkpoint file) works fine. The error occurs, the OnError event handler is called, the package stops and the checkpoint file is created.
When the package is restarted is goes to the correct spot (where the error occured) using the checkpoint file, then it throws an error within the For Loop container and does not call the OnError event handler. The OnError event handler is setup on the For Loop container. The ForLoop performs three loops. Each one of these loops creates an error. Not one of these errors within the three loops will trigger the OnError event handler...
Any help would be appreciated.
thanks
Bug: Sequence Containers And Checkpoint Files
Mar 3, 2008
There is a bug in SSIS2005 concerning the way that checkpoint files behave in concert with Sequence containers. It is documented (at length) here:
Is it possible to execute a container regardless of the checkpoint file?
(http://forums.microsoft.com/msdn/showpost.aspx?postid=1574262&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0)
Is anyone from Microsoft yet able to give a definitive answer to whether this will be fixed or not in Katmai? A yes or no answer would be very very much appreciated.
Regards
Jamie
[Microsoft follow-up]
Peculiarity Using CheckPoint Files And The OnPostExecute Event Handler
Oct 5, 2007Hello,
Upon completion of a package the OnPostExecute event is fired whether the package was successful or not. As expected.
Then, I setup the package to use CheckPoints. I create a Script Task and set the Dts.TaskResult to fail. When the package runs and fails the OnPostExecute event for the package does not fire! If I remove the CheckPoints from the package, the OnPostExecute event fires.
What's even stranger is that if I set the Script Task to succeed, and then rerun the package, the OnPostExecute event still doesn't fire!
Has anyone else noticed this? It seems to me that the OnPostExecute event should fire whenever the package completes as it does when CheckPoints are not used. Why would it not fire when using CheckPoints?
Thank you,
Langston
Threat Due To SQL Injection
Aug 21, 2006I have gone through a terrible situation a week ago. One website was working fine since couple of years and suddenly it was hacked. I checked the entire code in all the pages and it was not at all modified.
When I checked the database, I found that javascript was inserted in it. As soon as a recordset was displaying it, javascript redirected that page to the hacker's site.. This was the mechanism was used by hacker.
Which database can be more secure in such situations?
Nishith Shah
Integration Services :: Create SSIS Package Checkpoint-file Property With UNC Path
Aug 26, 2015I can set the propperty of the checkpoint file to a local drive, but not to a UNC path mapping, mapping to my host server. (loop back)
Example: "I:FILEFILE1$InputArchiveOntwikkel " is possible as checkpoint file property.
S11487O$InputArchiveOntwikkel is not possible, though this is the same folder on the local host.
For data source both unc path and drive mapping are allowed. Why this difference?
HELP!!! Cannot Import SSIS Package Files From .dtsx Files
Oct 8, 2007
Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current
SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!
Security Problems With Mdf And Ldf Files
Jun 13, 2006Hello!
I developed database driven VC++ application. I faced a problem, which is "how to protect my database against direct access". E.g. .when i copy data files from one server to another and then using to attach the database to the new server the data base files are opened with out asking password .
I use MS SQL Server 2000 enterprise Edition as a DBMS and appropriate database.
I want to make possible to manipulate with data in my database only through my client application.
1. How do I define SA password and instance name in silent mode of MS SQL 2000 EE installation with Mixed type of Authentication?
2. If my database be attached to my new instance. Is it possible to copy my database, attach it to another instance and get a direct access to its objects ?
I appreciate for a help.
SQL Security :: Shrinking Log Files With Defragmentation?
Aug 6, 2015I have a no of databases in full recovery model whose files are many times their datafiles. It is because these databases were copied from the development servers and in the development servers they were not taking the transactional log backups although once in the production server it is ensured that a transactional log is taken once in a day atleast. I plan to shrink the logfiles using the dbcc commands. However I am afraid that it may lead to severe defragmentation and performance hits.
We are using Sql Server 2008R2 enterprise edition which is clustered.
In this context my questions are:-
1)What is the best course to do the shrinking of log with out defragmentation?
2)Can I do the shrinking when the database is in use or is online in production?
3)Will the shrinking of the logfile improve the performance in any manner like that of the i/o operations or paging?
4)Can I do the shrinking of the log files alone without the shrinking of the corresponding data files?
SSIS And Security - Questions
Mar 15, 2007Hello,
I realize that I am confused about SSIS and security.
In BIDS, I work on and modify my packages. That part I understand. Then, I want to build my project, then deploy to SQL Server 2005. I know how to do that too (for the most part, please see below):
My confusion arises around the "Protection Level" options in the package properties. Right now I have everything set to "EncryptSensitiveWithUserKey". My understanding is that I need to change this in order to run my packages from SQL Server jobs, because only the creator of the package can currently run the job.
So my question is, since I want to deploy to SQL Server, don't I want to change the "Protection Level" to "ServerStorage"?
However, it will not let me change the protection level to ServerStorage. It says "The protection level, ServerStorage, cannot be used when saving to this destination"... presumably because it is attempting to save to the file system.
However, even when I built out my packages and saved to SQL Server, I could not change the protection level either.
Which is why I am really confused... Once you save your packages to SQL Server, how do you make changes to the packages, so that the changes are reflected in the packages stored on SQL Server?
There is some concept I am not understanding here.
Thanks for any help
SSIS Package Security
Mar 21, 2006In DTS we had an Owner password and User Password. The User pasword allowed someone to run and schedule the package, but not edit the contents. Owner allowed full /designer access.
I like the two levels of password security. What is the equivalent of this in SSIS? I can only seem to get ProtectionLevel security to work with a single password that seems to be required to run, deploy and execute. Looks like an all or nothing proposition unless i am missing something.
Using SSIS Security Features
Apr 22, 2007I have simple package and trying to use the security feature of ssis. I set the protection level as "EncryptSensitiveWithPassword" and also specify the password. But it doesn't prompts me for the password. Can anybody help me in that?
View 1 Replies View RelatedProblem With Security SSIS Agent Job
Jul 19, 2007Hi,
i have a problem when i schedule an agent on the sql server and i log off the sql server ,
the agent failed with this msg' in the event viewr :
Login failed for user 'sa'. [CLIENT: <local machine>].
the account that run the SQL AGENT is NT AUTHORITYNETWORKSERVICE.
i guess the problem is there but i can't find how to resolve it?
i'll be happy for any help.
10X.
Security Requirements For Running SSIS
Mar 29, 2006Can someone tell me what are the security watchouts there are in running SSIS or the SQL Server Agent? I am having trouble running a job on a package that runs fine through Integration Services. The only difference seems to be that SQL Server Agent is running the job on the schedule.
Does SQL Server Agent need to have certain rights?
Do I need to be part of a certain group besides Admin?
Does the package need to have a particular security for someone to run the job?
I was finally able to get the package set but now I can't schedule the thing to work.
Execution Of SSIS Package - Security Issues
Aug 31, 2006Hi all,
I am currently trying to execute my first SSIS package and am having a lot of trouble with (what i believe to be) SQL security. During the development of the package i was testing all my connection managers with the sa account. Now that i have deployed the package i want to use another account with less privelages so that my setup is more secure. I have created a new login which maps to both the database my SSIS package is using and also to the msdb database. I have created a schema and user in each of these databases which i had initially set with minimal rights. This caused certain parts of my package to fail. I then set up the login with a sysadmin server role which worked no problem at all. Finnally i then gave the schema for the user full rights and removed the sysadmin server role. once again the package fail. Surely i can't only be able to run the SSIS package with an account that has a sysadmin role?
Can anyone tell me if i am doing something wrong. The SQL server 2005 security model is so frustrating to get to grips with.
Many thanks in advance,
Grant
Ssis Security Short Coming, Major Issues
Jan 6, 2006Hello,
I feel like ssis encryption model has a serious flaw. Especially when linked to SQL Agent jobs.
I have posted and others have posted messages about this. Something is plain wrong with ssis encryption keys and password protection. Also, you do not have the choice not to protect the packages. In my case, protecting packages is completely useless.
Here is the story.
After this post
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=131340&SiteID=1&mode=1
I created config files for al my packages connections passswords.
Now, by our IT Policy, I had to change again my password and of course, all packages now return multiple errors when I open them.
Hopefully, the config file did its job and the packages are ran anyways by SQL Agent, however, having to manually retype and resave all packages not to have the errors is just a plain hassle. Not to speak about people not using the config files and the correct "Run As" sql agent account.
I stress the fact that in a real world production environment all packages are driven by SQL Agent jobs and MUST run automatically.
Here is the error I get after opening a package after changing my password:
Error 1 Error loading Constants05.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. c:projectsssis packagesssis constantsConstants05.dtsx 1 1
So Why is'nt this key automatically adjusted after Windows NT Domain password Change?
How can I refresh the key, not to have to reype all the packages connections passwords and rebuilding, Checkin-in again all the stuff?
I do not think the solution is "Use an application account which password never changes when you create your ssis packages" however at this time, this is the only solution I can think of.
How do you guys deal with this problem?
I still do not understand the ssis security model I feel it is diconnected from the reality and unpracticable in a production environment like mine.
Thanks
Philippe
Calling SSIS Package From Web Service - Security Issue
Nov 15, 2006I am trying to call a SSIS package from a web service hosted on the same machine as the package file is sitting. All that the package does is a simple Execute SQL task with one datasource connection.
I have set impersonation as true. When I run the package from the web service on the virual port (through Visual studio IDE) it runs fine.
http://localhost:4609/WebServiceRunSSIS/LaunchSSISPackageServiceCS.asmx
In the above case I don't think impersonation means anything coz it runs successfully even when there is no impersonation set!
I then run the same service (asmx page) from the browser after making it a web service on my machine
http://localhost/WebServiceRunSSIS/LaunchSSISPackageServiceCS.asmx
I get an error indicating package failure.
I have enabled logging in SSIS. The package log indicates the following:
Failed to acquire connection "APNetDS". Connection may not be configured correctly or you may not have the right permissions on this connection.
Effectively, my web service impersonated account (which is my admin account) is not being authenticated for any of the db connection (uses windows integrated authentication) and I find that odd. To impersonate, I have set the Impersonation to true in the web.config file. The authentication mode is windows.
Please tell me what bit of security I am missing? In case if it helps, the database connection is to my local database and hence should not deny access to my own account. And yes, I think my impoersonation is working because when I debug, the user.identity shows my user id.
Thank you,
Sumeet
Checkpoint
Mar 5, 2001Can i force a checkpoint?How?Will it have any implication?
TIA
Checkpoint
Aug 26, 2003Please help.
I would like to checkpoint my transaction log every night before full backup.
Would this affect the transaction log sequence in the event of a restore.
I run SQL Server 2K SP 3 on WIN 2K SP 3.
Thank you.
Regards
Checkpoint Not Being Issued
Oct 4, 2005Hi,
Has anybody encountered this situation before? DB on SQL Server 2000 SP4 with trunc log on chkpt option turned on. Checkpoint trace flags were turned on but noticing no checkpoints are being done on one specific DB resulting into growing transaction log. No open transactions.
Any ideas?
Thanks.
Checkpoint Process
Apr 12, 2007I see a line in sys.sysprocesses. The process's status is suspend and the command is CHECKPOINT. I have the information here exactly as it is on my monitor. It seems is consuming hi cpu. What should I do?
[/code]
spid: 10
kpid: 7416
block: 0
waittype: 0x0081
waittime: 232546
lastwaittype: CHECKPOINT_QUEUE
waitresource:
dbid: 1
uid: 1
cpu: 427046
physical_io: 36695
memusage: 0
login_time: 2007-04-04 10:01:32.787
lastbatch: 2007-04-04 10:01:32.787
ecid: 0
open_tran: 0
status: suspended
sid:0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
hostname:
program_name:
hostprocess:
cmd:CHECKPOINT nt_domain:
nt_username:
loginame: sa
[/code]
Canada DBA
Checkpoint Restart
Jan 24, 2006I have a package that uses checkpoint restart. It is resposible for truncatings many sets of tables and then loading them. There are several ExecuteSQL tasks to truncate the tables and several corresponding data flows to accomplish the loads.
If a load fails I want the corresponding truncate task to be part of the restart otherwise duplicate data may be loaded. Normally, SSIS will start at the failed task. I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
Anybody know how to accomplish what I want to do?
Unreliable Checkpoint
Nov 27, 2006With 2005 SP1. Have built a SSIS package that successfully saves a checkpoint file and sometimes successfully restarts. (I've also built some others that are 100% reliable).
On the unsuccessful restart it appears as though the failed steps and subsequent steps do not execute. the package appears to "complete" though and the checpoint file is removed as though everything is fine.
On a successful restart the failed step reexcutes and everything works fine.
The issue appears that when a failed step finishes at the same time as a successful step finishes there is contention in the process that writes the checkpoint file out and the checkpoint file is corrupt. The failing step runs in parallel with a successful step and the execution times are very similar so task A may complete before or after task B.
Contents of a good checkpoint file follows <DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:PackageID="{3BFFF2F9-74BA-4CE9-8435-81CC198E8144}"><DTS:Variables DTS:ContID="{3BFFF2F9-74BA-4CE9-8435-81CC198E8144}"/><DTS:Container DTS:ContID="{3655F83D-5EA5-4F16-9B8F-520582A1229A}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DB2D7A57-D405-4B11-AF4A-41B331EE3F15}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DFC6A95F-CCFA-4FD9-B604-FCBD722B47D8}" DTS:Result="0" DTS:PrecedenceMap="YYY"/></DTS:Checkpoint>
Contents of a bad checkpoint file follows
<DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:PackageID="{3BFFF2F9-74BA-4CE9-8435-81CC198E8144}"><DTS:Variables DTS:ContID="{3BFFF2F9-74BA-4CE9-8435-81CC198E8144}"/><DTS:Container DTS:ContID="{3655F83D-5EA5-4F16-9B8F-520582A1229A}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{9FAD4043-8D5F-4044-915A-87ECABDE6A7C}" DTS:Result="1" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DB2D7A57-D405-4B11-AF4A-41B331EE3F15}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DFC6A95F-CCFA-4FD9-B604-FCBD722B47D8}" DTS:Result="0" DTS:PrecedenceMap="YYY"/></DTS:Checkpoint>
Has anyone seen this behaviour before?
Checkpoint Not Being Set? Log Not Truncating?
Oct 26, 2007Hi,
I've set up a number of jobs (not a maintenance plan) via a script in SQL 2005. These jobs do the following:
1) Full backup every sunday night
2) Differential backup every weeknight
3) Log backup every hour
The database is obviously in the full recovery model.
The backups all seem to be running, with one issue - the log file is still growing and is not being truncated. I was under the impression that a log backup should result in the log being truncated after each full backup. However, this does not seem to be the case.
Is there anything obvious I've missed that needs to be set up, or is there a way I can check that the full backup is actually setting the appropriate checkpoint and that the log backups are 'seeing' these checkpoints?
Thanks
Transaction And Checkpoint
Dec 7, 2007If i have 3 Tasks in my control flow with checkpoint enabled and the transactionoption of the tasks is required,
Transaction option of the package is supported.
if the second task fails , the package restart from the first task when its running again instead of using the checkpoint and begin from the second task
Can anyone tell me if it€™s a bug in SSIS?
Zip Files From SSIS
Apr 2, 2007Do any one know who to zip the files.
Here is the deal... I am sending tab delimited files from one folder and i want to zip the file once the files are send and then delete those files as i would have backup in the format of zip file.
Do any one know regarding the aforesaid..
ie. folder X has 5 tab delimited files
once i have send those 5 files i want to zip them in another folder (folder Y ) with date stamp and then delete those 5 files.
SSIS And XML Files
Mar 25, 2008Hi,
I use an XML source to load an XML file to my db, so i genrate with sucess the xsd file but when i check OK i get an errer which is "Task mismatch of data streams [Source XML [1]] The XML source adapter does not support the model of mixed content on complex types",
"The component pipeline returned error code HRESULT 0xC02092A1 from a method call. (Microsoft.SqlServer.DTSPipelineWrap)".
Using System.Web.Security.Membership.DeleteUser In SSIS Package Script Component
Dec 4, 2007
Hi
I have to import a list of users and then add them to my Sql database via .NET framework APIs
Now the following code works from within my web application but I cannot get it to run in an ssis transormation script.
Has anyone got experience with this requirement in an SSIS package script?
Code Block
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Static users As New System.Collections.Hashtable
Dim password As String = String.Empty
If (Not users.ContainsKey(GetUserCode(Row.UserID))) Then
users.Add(GetUserCode(Row.UserID), GetUserName(Row.FullName))
'create a random password
password = System.Web.Security.Membership.GeneratePassword(7, 1)
Try
'insert new user
System.Web.Security.Membership.DeleteUser(GetUserCode(Row.UserID))
System.Web.Security.Membership.CreateUser(GetUserCode(Row.UserID), password)
'link roles to new user
System.Web.Security.Roles.AddUserToRole(GetUserCode(Row.UserID), "MANAGE_SCORECARD")
Catch ex As Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
End If
End Sub
Truncate On Checkpoint And Autoshrink?
May 9, 2002Details: MSDE 1.0 / SP4 - Windows 2000 Pro
I have a database that has Truncate on Checkpoint set for the Log file. The Log file is set to AutoGrow. Is it necessary to to run dbcc shrinkdb (or the like) to get Log file to contract? Is there any harm in not contracting the Log file? I'm looking for best efficiency and least-likely-to-fail path as DB sits 'really remote' and there is little opportunity for observation.
Does anyone have any recommendations on re-indexing? I have one table that bears the most growth. It has a clustered index. What would be a suitable data point to watch? I run a SP to save DBCC SHOWCONTIG info along with the duration of a test query, but haven't seen a clear breakover point.
TIA -RC
Truncate Log On Checkpoint Keeps Checking Itself.
Nov 15, 2000Can anyone assist with this problem.
Every now and then my overnight backups (backup Exec) fail due to the truncate log on checkpoint being enabled. This occasionally occurs on Master MSDB databases. I have unchecked the truncate log on checkpoint box numerous times and the backups work fine. Then mysteriously the box is checked again and the backups fail once more. I am stuck as to why this can happen. Is there a generic stored proceedure that checks this box ?
Help with this will be welcomed.
Truncate On Checkpoint Not Truncating
Dec 30, 1998The log on one of my databases keeps filling up, even though I have it set to truncate on checkpoint. the only real difference between this database and the others on my server is that it is built from the dump of another database (on another server) where the tables are marked for replication.
I'm wondering if the fact it is built from a replicating database could be causing this. I've noticed I can't drop any of the table, even though my database isn't set to replicate (or publish).
two questions
1) Any ideas?
2) Is there anyway I can make my server realize I'm not replicating so it will let me drop those tables? (nothing in Enterprise manager indicates that my database is replicating or publishing).
Thanks,
Jim
CHECKPOINT Backgroup Process
Apr 16, 2008Hello,
I am DB Developer (not admin), excuse me if this is a silly question.
I don't know much about CHECKPOINT background But I feel, this process is slowing down performance of my sps which runs slower than normal in some cases.
Especially when I see any of my process goes in the suspended mode and its wait type is SLEEP_BPOOL_FLUSH and CHECKPOINTs process is also suspended and its wait type is CHECKPOINT_QUEUE.
More important anything else is... this background process (which I always find its spid is 11) BLOCKS all other user processes when it goes into suspended mode and its wait type is SLEEP_BPOOL_FLUSH
I dont know my analysis is correct (claiming checkpoint as culprit), need experts advice and help
can someone give info on checkpoint and how this effect server performance
Server Freezes While Doing Checkpoint ?!
Jul 24, 2007We're running the Microsoft product SMS 2003 SP1 for software deployment, patching, hardware inventory, etc. The back-end is SQL 2000 Enterprise SP4 which is installed on the same box as the SMS 2003 SP1 product, and the DB is 145GB's.
We started noticing that the server would freeze every minute or so for 30 seconds. We started logging stats via perfmon and saw that the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 for 30 seconds at the same time the freezing occurred. I have determined that this is occurring during the checkpoint. The recovery interval option is set to the default of 0 on SQL, when I changed the setting to every 5 minutes, the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 every 5 minutes and would subside after 2 minutes. I understand the need for the checkpoint / recovery interval option, but don't believe this high average disk queue length should be occurring.
Does anyone know why this is happening and how to fix this ? The freezing of the box while checkpointing is killing me.
Thanks