I have installed sql server service pack 1 which is the same as a colleague of mine has and his package works...
How in name of goodness are we supposed to set a connectionstring that actually works...I have tried recreating the project and deleting a recreating the components but the message is still: Login failed for user 'sa'
This is absolutely pathetic from Microsoft to release such a bug-ridden system.
Have you setup correct login information on the connections?
What is the ProtectionLevel property of the package set to?|||
mj van den berg wrote:
I cannot take this anymore... I have installed sql server service pack 1 which is the same as a colleague of mine has and his package works...
How in name of goodness are we supposed to set a connectionstring that actually works...I have tried recreating the project and deleting a recreating the components but the message is still: Login failed for user 'sa'
This is absolutely pathetic from Microsoft to release such a bug-ridden system.
Many many other people have managed it (including your colleague) thus proving that the product is not bug-ridden. Perhaps you might want to look a bit closer to home before blaming anyone else.
Exactly where are you trying to apply the connection string? You are aware that under some circumstances SSIS will not store the password for you, right? That's is why Phil asked about ProtectionLevel
-Jamie
|||Check if you have the same user profile of your collegues!
Regards,
Pedro
|||
Check if you have the same user profile of your collegues!
Regards,
Pedro
He won't likely.|||
If your problem is in your company, check in active directory if you have the same groups of your collegues...
|||
Check if you have the same user profile of your collegues!
Regards,
Pedro
I have checked that. We are using the same user profiles. Both packages are set to EncryptSensitiveWithUserKey.
The thing is: I have both projects on my pc and my colleague and I are both trying to fix this and even though his works, none of us can explain why the thing fails since there are 2 of us who have confirmed it being the same. I am sorry if I sound like I am passing the blame to microsoft and will admit that I am new in SSIS (obviously) but the reason I'm so frustrated is that this is the first software package i have worked on in a long time where one fixes things by recreating the component or even the package from scratch and things will start to work.
I mean, every time I, for instance, delete a connection manager, the environment does not remove the component's ID from the dtsx file and I have to manually do it otherwise I get the message of the project being corrupt. This is an ongoing thing that I am experiencing and it's getting frustrating.
I have now recreated the package from scratch and all of a sudden some things are starting to work such as directory variables that point the 'for each loop' to a folder where flat files are to be processed....and I did not change anything compared to the previous package.
About the connection string, I have created an oledb connection manager and set the connection information there. It was tested and it connected. Then I defined it in a script's connection manager which i use in the script to execute a simple query which never fire's since I cant get the connectionstring to work...again, this is the same as the colleagues package.
I am sorry for sounding a tad over-frustrated yesterday...today will be better since my aircon is working again 
Regards
|||You may have already tested this, but if you are using EncryptSensitiveWithUserKey, only the user account that created the package will be able to decrypt passwords in it. So unless you and your colleague are logging on to the network with the same user ID, you shouldn't expect to run his packages or vice versa.|||Hi,
Thanks for the reply. We have created a user for this and both use that user.
I have spent a great number of hours recreating the project on a different machine, that I stole from our store room, using the same methods etc and it works!
It must be something that had gone corrupt on my machine which is strange since it is a brand new laptop. Our internal it guys set it up though including the development software so I'm going to do it myself now.
Thanks for the support.|||And you both log into the machine with the same user account on the same machine? If not the same machine, I believe the user key will be different and hence, your problem.|||
We have created a user for this and both use that user.
Hi Mj Van Den Berg,
I'm curious about this. What type of user did you create? Is this a SQL Server login or an Active Directory account?
Thanks,
Andy
|||I'm thinking the same as Andy here (I think
).The "userkey" points to the user you use to log in to windows, not the user to log in on the DB. So if your colleague made a package and you used it, this would mean you, on your system, would not be able to decrypt the connection info for the datasource.
|||Hi there,
The user account is a sql server account, which is why we could log on again after recreating it on the other machine.
I have played around setting the package security to almost every option (even those I knew wouldn't work) but it went bad.
I finally decided to rebuild my machine when my colleague told me that his environment wouldn't go corrupt when he, for instance deletes an existing data connection-manager and that he rarely had to manually edit the dtsx file and another non SSIS project of mine started to act up with it's designer not functioning properly as well.
Regards
|||
Hi Mj Van Den Berg,
Rebuilding your laptop may or may not correct the issue. I hope it works for you.
To be sure I'm understanding the scenario:
1. You and your colleague both build SSIS packages.
2. Your colleague's SSIS packages execute - even when you log into the domain as yourself from your laptop and execute your colleague's SSIS packages manually.
3. SSIS packages you author execute partially (you can get some connections to work now but cannot connect from a script task).
Please clarify.
Here's some of my thinking:
1. Connectivity in SSIS is no simple matter. Understanding connectivity is one of the steeper ledges on the SSIS learning curve. There are multiple moving parts: Data Connections are stored on the development workstation; Connection Managers are managed at either the solution or package level, depending on how they're defined; Source and Destination Adapters can be stand-alone objects in a Data Flow or integrated into Data Flow components or Control Flow Tasks.
2. SSIS Security is an equally steep ledge on the SSIS learning curve. Information considered "Sensitive" is never maintained as clear text. This means if you:
a. define an OLE DB Connection Manager using the sa account and proper password,
b. check the "Save my password" checkbox,
c. test the connection (and the test is successful),
d. close the Connection Manager editor,
e. browse to the Connection Manager Properties,
f. highlight and copy the ConnectionString value, and
g. paste this value into the ConnectionString property of an ADO.Net connection you are establishing from a Script Task;
the ADO.Net connection inside the Script Task will always fail because it is missing the password property - while the Source and Destination Adapters that reference the OLE DB Connection Manager will successfully connect. This is all by design. I'm not saying this is what you did but if you did, all you have to do to make this ADO.Net connection work inside the Script Task is simply append text like "Password=[MySaAccountPassword];" to the ADO.Net ConnectionString property inside the Script Task.
3. SSIS connectivity and security interact. The behavior is controlled largely by the ProtectionLevel property of the package but it's important to realize that, in an Active Directory domain, your Active Directory user profile can play an important role in package execution - depending on how you define connectivity and security in your SSIS solution and package. The default ProtectionLevel property setting is EncryptSensitiveWithUserKey. This setting means your Active Directory credentials are used to determine your rights to connect to any data source (SQL Server, directory, file) outside the package - unless you explicitly provide security information (username \ password). If your Active Directory user profile is identical (all SQL Server, directory, file permissions are the same) to that of your colleague, it's likely your laptop rebuild will correct some of the issues you're experiencing. If your user profiles differ, rebuilding the laptop may not correct the issues.
From your three posts above, I understand you don't like the fact you cannot delete connection managers completely from your SSIS package by simply clicking on them and pressing the Delete key. This issue can be the result of:
1. Corrupt package metadata,
2. A less-than-up-to-date installation of SSIS (SP2 is current),
3. An incorrect or corrupt installation of SSIS.
It's important to consider all of these scenarios when troubleshooting these types of issues. I advise considering them in the order listed.
In my opinion, it's better to encounter and address these issues now. Most folks learn about them on deployment day which is far more painful than during development.
Andy
|||Hi Andy,Thank you very much for the in-depth and helpful post.
I have rebuilt my laptop and updated every software package that I could think of and, thank goodness, it works!
I'm sure that I must have had a corrupted installation of SSIS or even SQL server 2005 otherwise the problem would have persisted. For informative purposes, I want to elaborate that we did use a password variable which is passed through to the script which is used to take care of the problem of it failing due to the password property.
I am still new to the SSIS package and I should have known better to assume that because it is fairly new that it does behave as strangely as it did on my pc. There was also some strange behavior in SQL server's management studio bombing out frequently but I only picked this up after my issues with SSIS.
Thanks for all the help
Regards
Marcel
No comments:
Post a Comment