I have an SQL Server where only a group of sysadmins have access to install DTSX packages. Those DTSX packages are developed by another team that does not have access to the production SQL Server. They use their own SQL Server.
In order to make it as simple as possible to install these packages by the sysadmins, I suggested the use of configuration files. The files are associated with the job that executes the package and all that has to be done to install the package is copy it to the file system or import it into the SQL Server. Developers use their configuration file, sysadmins user theirs. Nothing new here.
The problem is that some of the packages have to access some old systems and we cannot use integrated authentication. We have to use SQL authentication and therefore specify a user account and password in the connection string. If this is stored in the configuration file, it is available in clear text! If I store the configuration in the package itself using ProtectSensitiveWithPassword protection level, the sysadmins will have to edit every DTSX package to reset the connections to the production environment (the developers always send them with their development configurations) and I don't want that. If I store it in a SQL Server database, it seems the sysadmins also have to edit the package to point the package configuration to the correct database and set the configuration filter.
Another solution is to store the credentials in clear text in the configuration file but set the file system permissions on that file so only the account that executes the package can read them (this is what I'm implementing if nothing better comes up...)
Is there any other way to do this? Am I doing something wrong?
Thanks in advance.
Rui Covelo wrote:
Hi!
Another solution is to store the credentials in clear text in the configuration file but set the file system permissions on that file so only the account that executes the package can read them (this is what I'm implementing if nothing better comes up...)Is there any other way to do this? Am I doing something wrong?
Thanks in advance.
I don't see any other way. Unless that you want to save you the time of oppening each package to change the credentials in every package; I think you are better dealing with the passwords in plain text and securing the place where they live. BTW, you can use Env. variables or a SQL table to place those connection strings if that makes you feel more secure.
This is just my opinion and not an entry for the 'best practice' book
Rafael Salas
|||I don't see using configuration files is not that big issue that people seem to think of it. So what if they use clear text, all that means is that you need secure them correctly. You probably have a password store already. Does it have password complexity? Does it have group membership? Do passwords expire? Can you manage access centrally? If not it would seem obvious that AD is going to do a better job, so use it :)
The issue I think is more about how people have viewed file permissions in the past, or rather the lack of them. Use them correctly and will be more secure than a single password embedded in a package, and don't forget those jobs that also have the password too. AD is much harder to circumvent any controls around it. People just don't use file permissions, because it is "hard", well get it right and it is more secure, so what sounds better?
|||I would re-iterate what Darren has said.
At some point someone somewhere needs to know what some password is to open up access to a securable. If that password is the password of a Windows AD account then so much the better. Security is one of the main things that AD is there for.
-Jamie
|||Thanks for your answers!
No comments:
Post a Comment