Deployment of system

I am about to start developing a system using VB 2003 and SQL Server 2000. On my development computer, I am going to use MSDE for the database, which I guess I will later be able to export to my client's SQL server 2000.

Now, I am a bit worried. In my local computer, I can develop a system which runs locally very well. So, I am very sure I will be able to develop one for my client. But when I later deploy it to the client, it will have to run on a server and client machines.

My question is, how easy or complex is it to deploy such a system from my standalone platform to a client-server one. I have adequate experience developing stand-alone systems.

I am about to get started so please kindly give me ideas on the best path to take.

Hi there and welcome to client server development :-)

Its quite easy if you are developing on a MSDE and want to deploy that later on a Std / Ent Server. It is reduced to making a backup and restoring the backup on the server, normally that should be all except for the server users, which additionally have to be scripted and deployed.

BTW: If you want to deploy it on productional systems on Std / Ent Editions, why don′t you use SQL Server Dev Edition to develop the applications. It would be more comfortable for you and it would be a more soution-near development.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanx a lot for the info.

What I am about to develop is really a small system that would not warrant me going for any extra development tools more than what I have - cost is an issue.

You said that the database it is a matter of backup/restore. What about the VB application? Lets take the simplest example below.

Supposing I have a VB form bound to a datasource coming from some SQL Connection (localhost) on my development PC. The form will run on my computer because its connection is there.

Now, what happens if I take the above (app and database) to client-server environment. I restore the db on server and install app on client (?). But the connection in app is to localhost on my development PC. So it will not run in client-server until I modify the connection to point to the new server. Am I right or wrong?

|||Hi,

yes you are right, but that a point of configuration. I think right now you are using configurable settings in your application, so just add another setting to pass the servername, port etc. to the data source creator (or whatever you implemented).

The developer edition isn′t expensive at all. Should be available for 49$ (around),so developing on development edition will help you in many cases spending (/wasting) time for configuring something or getting a fst info from the server.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||Be blessed. I'll check out all your suggestions.

Deployment of a C# Windows Application connected to SQL Server 2005 Database

Hello all,
I need to consult you in the following problem:
I am developing a windows application in Visual C# 2005 , connected to
an SQL Server 2005 database ,
I want to make deployment for the application so that the user can
have a copy of the application and the database and use it on his
machine even if they dont install the sql server or the visual
studio , i made it using Setup Projects. It was built successfully ,
and created Setup project, when i tested it on another machine ,it
displays the installation wizard ,but it displays an error when trying
to make connection to the database .. the error is:
An error has occurred while establishing a connection to a server ,
when connecting to SQL server 2005,...Could not open a connection to
sql server 2005..
also i logically feel that it is wrong to write an sqlconnection
statement with the name of my machine as the data source , and how to
copy SQL database to the user machine ?
What shall i do? Please Reply...(compjor@.yahoo.com) writes:
> I am developing a windows application in Visual C# 2005 , connected to
> an SQL Server 2005 database ,
> I want to make deployment for the application so that the user can
> have a copy of the application and the database and use it on his
> machine even if they dont install the sql server
Eh? An SQL Server database without SQL Server installed is quite much
a useless piece of bytes.

> i made it using Setup Projects. It was built successfully ,
> and created Setup project, when i tested it on another machine ,it
> displays the installation wizard ,but it displays an error when trying
> to make connection to the database .. the error is:
> An error has occurred while establishing a connection to a server ,
> when connecting to SQL server 2005,...Could not open a connection to
> sql server 2005..
You don't connect to the database. You connect to SQL Server which
will then access the database. But if there is no database to connect to?

> also i logically feel that it is wrong to write an sqlconnection
> statement with the name of my machine as the data source , and how to
> copy SQL database to the user machine ?
There are two possible scenarios:
1) The user already has SQL Server installed on his machine, and don't
want another instance. In this case, give him the option to attach
the database to his server.
2) Bundle SQL Express with your setup, install SQL Express as part of your
setup and attach the database to it.
In both cases you would include the database file in your setup. (Actually,
I would rather use a backup, that the data files directly.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

deployment issue

hi,
Thanks for ur reply in newsgroup. Actually i'm developing
a webapplication called dashboard in which while the user
want to drillthrough the report shd open in report
designer. It shd be dynamic. is there any possiblity to do
this with out vs.net ? if we have to use vs.net , then
how can we do this ?
can u help me in this
thanks in advance,You could deploy the report and access it via URL.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"alex" <anonymous@.discussions.microsoft.com> wrote in message
news:36dd01c47203$9f637420$a601280a@.phx.gbl...
> hi,
> Thanks for ur reply in newsgroup. Actually i'm developing
> a webapplication called dashboard in which while the user
> want to drillthrough the report shd open in report
> designer. It shd be dynamic. is there any possiblity to do
> this with out vs.net ? if we have to use vs.net , then
> how can we do this ?
> can u help me in this
> thanks in advance,
>

Deploying RS reports with XML datasources?

Hello-

We've been developing RS 2005 reports which consume XML data from several webservices we've written.

Now comes the issue of deployment. Our production server is not our development server. We've been using the development server URL for our connection string when we built our datasets... how does that work when we want to deploy these reports?

I suppose we can edit the RDL of each report and manually change the connection strings to reflect the the production server address. Is there a better practice that we can implement to handle this problem?

Thanks.

Have you tried to use Shared Datasources? This typically eliminates having to store the information in the report.

Deploying Reports to a different Environment

I am developing some reports and the only way I can deploy the reports to
the production server is via files on a CD.
The project manager would like to encapsulate everything into an .MSI file
for deployment.
Is there any documentation on whether this can be done? or how to go about
deploying reports when all I have is a bunch of files
Thanks
BobYou can deploy using RM from VS or through scripts. Best way for you is to
create scripts and put it in a batch file.
Amarnath,MCTS
"Bob" wrote:
> I am developing some reports and the only way I can deploy the reports to
> the production server is via files on a CD.
> The project manager would like to encapsulate everything into an .MSI file
> for deployment.
> Is there any documentation on whether this can be done? or how to go about
> deploying reports when all I have is a bunch of files
> Thanks
> Bob
>
>

Deploying Jet OleDb Provider

If I'm posting to the wrong forum, please correct me.

I'd like to use an Access database for an application I'm developing for retail sale, and I need to do these things:

Determine during setup if the target machine has the Jet OleDb Provider installed. If so, determine whether the version is adequate. If necessary, install the correct provider.

I don't seem to be able to find any documentation or downloads covering this scenario, however. This seems odd, since I would have imagined the scenario to be rather common.

Has anyone had any successful experience with these issues?

Thanks

Hi,

if you are having a installer package, you can query the registry for the MDAC key which is described and worked for me in many cases here:

http://support.microsoft.com/?kbid=301202

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

That looks good, thanks.

Would you happen to know where I can get a redistributable Jet provider? I'm looking here:

http://msdn.microsoft.com/data/mdac/downloads/

But all of those are user-initiated setups. You're correct—I'm using an installer.

|||Hi there,

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257604

HTH, Jens Suessmeyer.

PS: The one included in VS is only to check the existence of MDAC.

http://www.sqlserver2005.de
|||

Ah, you're the greatest!

|||:-D Nice to hear that. :-D

-Jens.|||

Say, tell me... did you look those articles up specifically for this thread, or did you already have them on hand.

The reason I ask is because of the cumbersome task of sifting through the KB. I know I probably shouldn't, but lately I've pretty much given up on trying to find anything useful there among the myriad of articles that don't apply to the issue I'm looking for.

You must be pretty good at it.

|||Thats something in the middle, for the most cases I ran into the same problems / issues like the posters. Knowing the *right* keywords from my experience its easy to find the right articles on the internet.

-jens.|||

Ah, I see. Makes sense.

Tell me—since clearly you've already fought this battle—do you have your programs install MDAC in order to support Access, or do you instead opt for the OleDb provider?

The reason I ask is, according to my research on the subject, MDAC stopped including it after version 2.5. In fact, the second KB article you referred me to (thanks again) discusses MDAC only, and not OleDb.

That said, have you even been able to find a silent install for the standalone OleDb provider?

Thanks.

Deploying CLR Stored Procedures to a client, is there a better way

I'm developing an application locally, and after I complete major revisitions/bugfixes, I transfer the code/database changes to the client. If I change the code for a CLR stored procedure locally, I can just deploy the project to the local database, which handles everything for me.

Transferring to the client's database is more difficult, however. In order to update the CLR procedures, I need to drop my regular stored procedures, drop my functions, drop the CLR procs, and then re-add the CLR procs, functions and stored procedures to avoid errors based on dependencies. The stored procs/functions don't change, and it seems like there should be a way to just insert the new CLR stuff without dealing with the regular stored procedures or userdefined functions.

Basically, I'm asking whether there is a better way to do this, or if I'm stuck doing this excessive routine every time I need to update.

Thanks in advance

it sounds like your in the "debug" phase right now, so you could use multiple assemblies to seperate those SQLCLR routines that you know are still giving you problems and those that are not. Once fully tested you could then re-merge them back into one assembly.

You could also look at using the scripting wizard to script the assembly and all of it's dependencies from your local/dev box.

Derek

|||

Have you tried using ALTER ASSEMBLY from T-SQL to deploy changes to the assembly.

Detailed documentation on ALTER ASSEMBLY is available at http://msdn2.microsoft.com/en-us/library/ms186711(d=ide).aspx

Thanks,

-Vineet

Deployed report with custom assembly fail when calling web service

Hi,
I'm developing a report that calls a web service using a custom assembly.
I've got the code working in VS.NET, but when I deploy to my local Report
Server, I get this:
System.Security.SecurityException: Request for the permission of type
System.Security.Permissions.SecurityPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 failed
I have added the following to RSReportServer.config:
<CustomAssemblies>
<Assembly Name="cmcs.wsclient.dll, Version=1.0.xxx.xxxx,
Culture=neutral, PublicKeyToken=xxx">
<PermissionSet class="System.Security.PermissionSet">
<IPermission
class="System.Security.Permissions.SecurityPermission"
version="1"
Flags="Execution, Assertion"
/>
<IPermission class="System.Security.Permissions.WebPermission"
version="1"
Unrestricted="true"
/>
<IPermission
class="System.Security.Permissions.EnvironmentPermission"
version="1"
Unrestricted="true"
/>
</PermissionSet>
</Assembly>
<Assembly Name="cmcs.msrs.webserviceclient, Version=1.0.xxx.xxxx,
Culture=neutral, PublicKeyToken=xxx">
<PermissionSet class="System.Security.PermissionSet">
<IPermission
class="System.Security.Permissions.SecurityPermission"
version="1"
Flags="Execution, Assertion"
/>
<IPermission class="System.Security.Permissions.WebPermission"
version="1"
Unrestricted="true"
/>
<IPermission
class="System.Security.Permissions.EnvironmentPermission"
version="1"
Unrestricted="true"
/>
</PermissionSet>
</Assembly>
<Default>
<PermissionSet class="System.Security.PermissionSet">
<IPermission
class="System.Security.Permissions.SecurityPermission"
version="1"
Flags="Execute"
/>
</PermissionSet>
</Default>
</CustomAssemblies>
In rssrvpolicy.config, I have changed the PermissionSet for
Report_Expressions_Default_Permissions to FullTrust and added the following:
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Attributes="LevelFinal"
Name="ReportServer-cmcs.msrs.webserviceclient"
Description="Codegroup generated by the .NET
Configuration tool">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="file://C:/Program
Files/Microsoft SQL Server/MSSQL/Reporting
Services/ReportServer/bin/cmcs.msrs.webserviceclient.dll"/>
</CodeGroup>
It works to call a HelloWorld method returning a string from the custom
assembly, so there is no total failure, but calling the method to invoke the
web service fails.
Please help, I feel that I'm totally stuck!
JonasBy moving my CodeGroup in rssrvpolicy.config to right under the first
default CodeGroup, it I got it to work ...
<CodeGroup
class="FirstMatchCodeGroup"
version="1"
PermissionSetName="Nothing">
<IMembershipCondition
class="AllMembershipCondition"
version="1"
/>
***** RIGHT UNDER HERE *****
It seems like my CodeGroup wasn't used by ReportServer until the move. We'll
just have to see how long time it will work before it breaks again ;-)
Jonas" <Jonas@.nospam.pl> wrote in message
news:u$YVj30IGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm developing a report that calls a web service using a custom assembly.
> I've got the code working in VS.NET, but when I deploy to my local Report
> Server, I get this:
> System.Security.SecurityException: Request for the permission of type
> System.Security.Permissions.SecurityPermission, mscorlib,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
failed
> I have added the following to RSReportServer.config:
> <CustomAssemblies>
> <Assembly Name="cmcs.wsclient.dll, Version=1.0.xxx.xxxx,
> Culture=neutral, PublicKeyToken=xxx">
> <PermissionSet class="System.Security.PermissionSet">
> <IPermission
> class="System.Security.Permissions.SecurityPermission"
> version="1"
> Flags="Execution, Assertion"
> />
> <IPermission class="System.Security.Permissions.WebPermission"
> version="1"
> Unrestricted="true"
> />
> <IPermission
> class="System.Security.Permissions.EnvironmentPermission"
> version="1"
> Unrestricted="true"
> />
> </PermissionSet>
> </Assembly>
> <Assembly Name="cmcs.msrs.webserviceclient, Version=1.0.xxx.xxxx,
> Culture=neutral, PublicKeyToken=xxx">
> <PermissionSet class="System.Security.PermissionSet">
> <IPermission
> class="System.Security.Permissions.SecurityPermission"
> version="1"
> Flags="Execution, Assertion"
> />
> <IPermission class="System.Security.Permissions.WebPermission"
> version="1"
> Unrestricted="true"
> />
> <IPermission
> class="System.Security.Permissions.EnvironmentPermission"
> version="1"
> Unrestricted="true"
> />
> </PermissionSet>
> </Assembly>
> <Default>
> <PermissionSet class="System.Security.PermissionSet">
> <IPermission
> class="System.Security.Permissions.SecurityPermission"
> version="1"
> Flags="Execute"
> />
> </PermissionSet>
> </Default>
> </CustomAssemblies>
> In rssrvpolicy.config, I have changed the PermissionSet for
> Report_Expressions_Default_Permissions to FullTrust and added the
following:
> <CodeGroup class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Attributes="LevelFinal"
>
Name="ReportServer-cmcs.msrs.webserviceclient"
> Description="Codegroup generated by the .NET
> Configuration tool">
> <IMembershipCondition class="UrlMembershipCondition"
> version="1"
> Url="file://C:/Program
> Files/Microsoft SQL Server/MSSQL/Reporting
> Services/ReportServer/bin/cmcs.msrs.webserviceclient.dll"/>
> </CodeGroup>
> It works to call a HelloWorld method returning a string from the custom
> assembly, so there is no total failure, but calling the method to invoke
the
> web service fails.
> Please help, I feel that I'm totally stuck!
> Jonas
>

DEPLOY packages from Server A to Server B.

I developed some SSIS packages in SQL2005.When developing , The connect is set with Server A.

And i know ,later we are supposed to move to SERVER B.but B's IP and machine name are both unknown for developers.

The deployment guys will be cahrge in that.

what's easiest way for me to tell the deployment guy to add a connection to SERVER B after the SSIS packages have been developed?

any suggestion? Thanks in advance.

Use package configurations to store the data that can change (database connection strings, etc...)|||

HI phil:

yes , i tried this way.

In SERVER B ,imported .dtsx file , then changed the connection value with B server in .dtsconfig file, and ran the package, i found the package still affect the A server.

what' s wrong with my steps?

|||Do you have the ConnectionString property in the .dtsconfig file? This is what you should change.|||

Hi Phil:

Please take a look at the following content in my configure file(sorry for too much lines),could u tell me where to modify?:

Thank u very much

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="ASIAPACIFIC\daij" GeneratedFromPackageName="Package" GeneratedFromPackageID="{B780B7CC-3E98-4F5D-AF9A-E70F262B6FEA}" GeneratedDate="2007-3-4 18:55:47"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=SCN4685;User ID=sa;Initial Catalog=DemoDB0;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>DemoDB0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[Name]" ValueType="String"><ConfiguredValue>SCN4685.DemoDB0.sa</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ServerName]" ValueType="String"><ConfiguredValue>SCN4685</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[UserName]" ValueType="String"><ConfiguredValue>sa</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CertificateContext]" ValueType="Int64"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CheckpointFileName]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CheckpointUsage]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CheckSignatureOnLoad]" ValueType="Boolean"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CreationDate]" ValueType="DateTime"><ConfiguredValue>3/4/2007 5:32:13 PM</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CreatorComputerName]" ValueType="String"><ConfiguredValue>SCN4685</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CreatorName]" ValueType="String"><ConfiguredValue>ASIAPACIFIC\daij</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[DelayValidation]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[Disable]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[DisableEventHandlers]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[EncryptCheckpoints]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[FailPackageOnFailure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[FailParentOnFailure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[ForcedExecutionValue]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[ForceExecutionResult]" ValueType="Int32"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[ForceExecutionValue]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[InteractiveMode]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[IsolationLevel]" ValueType="Int32"><ConfiguredValue>1048576</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[LocaleID]" ValueType="Int32"><ConfiguredValue>2052</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[LoggingMode]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[MaxConcurrentExecutables]" ValueType="Int32"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[MaximumErrorCount]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[Name]" ValueType="String"><ConfiguredValue>Package</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[OfflineMode]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[PackagePassword]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[PackagePriorityClass]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[PackageType]" ValueType="Int32"><ConfiguredValue>5</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[SaveCheckpoints]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[SQLFolder]" ValueType="String"><ConfiguredValue>\</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[SuppressConfigurationWarnings]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[TransactionOption]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[UpdateObjects]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[UpdatePackage]" ValueType="Boolean"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[VersionBuild]" ValueType="Int32"><ConfiguredValue>7</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[VersionComments]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[VersionMajor]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[VersionMinor]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[BypassPrepare]" ValueType="Boolean"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[CodePage]" ValueType="UInt32"><ConfiguredValue>1252</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[Connection]" ValueType="String"><ConfiguredValue>SCN4685.DemoDB0.sa</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[DelayValidation]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[Description]" ValueType="String"><ConfiguredValue>Execute SQL Task</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[Disable]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[DisableEventHandlers]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[FailPackageOnFailure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[FailParentOnFailure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[ForcedExecutionValue]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[ForceExecutionResult]" ValueType="Int32"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[ForceExecutionValue]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[IsolationLevel]" ValueType="Int32"><ConfiguredValue>1048576</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[IsStoredProcedure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[LocaleID]" ValueType="Int32"><ConfiguredValue>2052</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[LoggingMode]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[MaximumErrorCount]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[Name]" ValueType="String"><ConfiguredValue>Execute SQL Task</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[ResultSetType]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[SqlStatementSource]" ValueType="String"><ConfiguredValue>usp_insertSSISLOG</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[SqlStatementSourceType]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[TimeOut]" ValueType="UInt32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[TransactionOption]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration></DTSConfiguration>

|||This is the only line you should have in your configuration file for connections: (The ConnectionString property)

<Configuration

ConfiguredType="Property"

Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ConnectionString]"

ValueType="String"><ConfiguredValue>Data Source=SCN4685;User

ID=sa;Initial

Catalog=DemoDB0;</ConfiguredValue></Configuration>

But, this seems to be using an ODBC connection. So, that means the ODBC connection has to change to point to the new database, OR, you have to change the "Data Source=XXXXX" parameter in the above line to point to the different ODBC connection. (Or Oracle connection, whatever it may be.)|||

Anyway，many many thanks

|||

Hahhahhha~~~~

Sometimes it‘s really weird!
2 whole days, I can’t figure out how to change the connection when deploying SSIS to another Server.
Today, I was so disappointed that I haven’t got the right way to work it round.
Before the shift is off, I fall a snap. And after snap, I used the XML spy to modify the configure file with password adding.
Hah~~it works!
I still wonder why the password is blank when we enabled the configure file option.
Here are my tips to change connections when deploying to Pro environment.

No need to change this:
<Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=SCN4685;User ID=sa;Initial Catalog=DemoDB0;Persist Security Info=True;</ConfiguredValue></Configuration>

But we have to change these property’s value:
InitialCatalog:
<Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>DemoDB1</ConfiguredValue></Configuration>
Password(what is blank at first):
<Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[Password]" ValueType="String"><ConfiguredValue>XXXXXX</ConfiguredValue></Configuration>
Servername:
<Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ServerName]" ValueType="String"><ConfiguredValue>SCN4685</ConfiguredValue></Configuration>

Gosh~~thanks.
It really works.

DEPLOY packages from Server A to Server B.

I developed some SSIS packages in SQL2005.When developing , The connect is set with Server A.

And i know ,later we are supposed to move to SERVER B.but B's IP and machine name are both unknown for developers.

The deployment guys will be cahrge in that.

what's easiest way for me to tell the deployment guy to add a connection to SERVER B after the SSIS packages have been developed?

any suggestion? Thanks in advance.

Use package configurations to store the data that can change (database connection strings, etc...)|||

HI phil:

yes , i tried this way.

In SERVER B ,imported .dtsx file , then changed the connection value with B server in .dtsconfig file, and ran the package, i found the package still affect the A server.

what' s wrong with my steps?

|||Do you have the ConnectionString property in the .dtsconfig file? This is what you should change.|||

Hi Phil:

Please take a look at the following content in my configure file(sorry for too much lines),could u tell me where to modify?:

Thank u very much

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="ASIAPACIFIC\daij" GeneratedFromPackageName="Package" GeneratedFromPackageID="{B780B7CC-3E98-4F5D-AF9A-E70F262B6FEA}" GeneratedDate="2007-3-4 18:55:47"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=SCN4685;User ID=sa;Initial Catalog=DemoDB0;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>DemoDB0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[Name]" ValueType="String"><ConfiguredValue>SCN4685.DemoDB0.sa</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ServerName]" ValueType="String"><ConfiguredValue>SCN4685</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[UserName]" ValueType="String"><ConfiguredValue>sa</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CertificateContext]" ValueType="Int64"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CheckpointFileName]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CheckpointUsage]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CheckSignatureOnLoad]" ValueType="Boolean"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CreationDate]" ValueType="DateTime"><ConfiguredValue>3/4/2007 5:32:13 PM</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CreatorComputerName]" ValueType="String"><ConfiguredValue>SCN4685</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[CreatorName]" ValueType="String"><ConfiguredValue>ASIAPACIFIC\daij</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[DelayValidation]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[Disable]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[DisableEventHandlers]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[EncryptCheckpoints]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[FailPackageOnFailure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[FailParentOnFailure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[ForcedExecutionValue]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[ForceExecutionResult]" ValueType="Int32"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[ForceExecutionValue]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[InteractiveMode]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[IsolationLevel]" ValueType="Int32"><ConfiguredValue>1048576</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[LocaleID]" ValueType="Int32"><ConfiguredValue>2052</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[LoggingMode]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[MaxConcurrentExecutables]" ValueType="Int32"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[MaximumErrorCount]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[Name]" ValueType="String"><ConfiguredValue>Package</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[OfflineMode]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[PackagePassword]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[PackagePriorityClass]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[PackageType]" ValueType="Int32"><ConfiguredValue>5</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[SaveCheckpoints]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[SQLFolder]" ValueType="String"><ConfiguredValue>\</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[SuppressConfigurationWarnings]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[TransactionOption]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[UpdateObjects]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[UpdatePackage]" ValueType="Boolean"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[VersionBuild]" ValueType="Int32"><ConfiguredValue>7</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[VersionComments]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[VersionMajor]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[VersionMinor]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[BypassPrepare]" ValueType="Boolean"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[CodePage]" ValueType="UInt32"><ConfiguredValue>1252</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[Connection]" ValueType="String"><ConfiguredValue>SCN4685.DemoDB0.sa</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[DelayValidation]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[Description]" ValueType="String"><ConfiguredValue>Execute SQL Task</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[Disable]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[DisableEventHandlers]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[FailPackageOnFailure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[FailParentOnFailure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[ForcedExecutionValue]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[ForceExecutionResult]" ValueType="Int32"><ConfiguredValue>-1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[ForceExecutionValue]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[IsolationLevel]" ValueType="Int32"><ConfiguredValue>1048576</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[IsStoredProcedure]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[LocaleID]" ValueType="Int32"><ConfiguredValue>2052</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[LoggingMode]" ValueType="Int32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[MaximumErrorCount]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[Name]" ValueType="String"><ConfiguredValue>Execute SQL Task</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[ResultSetType]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[SqlStatementSource]" ValueType="String"><ConfiguredValue>usp_insertSSISLOG</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[SqlStatementSourceType]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[TimeOut]" ValueType="UInt32"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[TransactionOption]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration></DTSConfiguration>

|||This is the only line you should have in your configuration file for connections: (The ConnectionString property)

<Configuration

ConfiguredType="Property"

Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ConnectionString]"

ValueType="String"><ConfiguredValue>Data Source=SCN4685;User

ID=sa;Initial

Catalog=DemoDB0;</ConfiguredValue></Configuration>

But, this seems to be using an ODBC connection. So, that means the ODBC connection has to change to point to the new database, OR, you have to change the "Data Source=XXXXX" parameter in the above line to point to the different ODBC connection. (Or Oracle connection, whatever it may be.)|||

Anyway，many many thanks

|||

Hahhahhha~~~~

Sometimes it‘s really weird!
2 whole days, I can’t figure out how to change the connection when deploying SSIS to another Server.
Today, I was so disappointed that I haven’t got the right way to work it round.
Before the shift is off, I fall a snap. And after snap, I used the XML spy to modify the configure file with password adding.
Hah~~it works!
I still wonder why the password is blank when we enabled the configure file option.
Here are my tips to change connections when deploying to Pro environment.

No need to change this:
<Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=SCN4685;User ID=sa;Initial Catalog=DemoDB0;Persist Security Info=True;</ConfiguredValue></Configuration>

But we have to change these property’s value:
InitialCatalog:
<Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>DemoDB1</ConfiguredValue></Configuration>
Password(what is blank at first):
<Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[Password]" ValueType="String"><ConfiguredValue>XXXXXX</ConfiguredValue></Configuration>
Servername:
<Configuration ConfiguredType="Property" Path="\Package.Connections[SCN4685.DemoDB0.sa].Properties[ServerName]" ValueType="String"><ConfiguredValue>SCN4685</ConfiguredValue></Configuration>

Gosh~~thanks.
It really works.

Deny Administrator Access to DB

Hey all,
I am developing an application to track very sensitive company personnel
information. This of course raised the issue of protecting this sensitive
information from everyone not authorized to see it.....including the DB
Administrator. The DB Server (SQL 7) has many databases on it, but the
System Administrator must not be able to access the data in those tables
containing the sensitive information. I have tried denying an SA user
rights to SELECT from a table, but I could still select and view all the
data.
Any ideas?
Jeremy Byron
jbyron@.cynaptec.nb.ns.ca <remove ns to email me>You cannot stop SA from looking at the tables...
HOWEVER - you CAN log the users that view the data.
For the tables that contain sensitive information, you can put a trigger on
the select, update, and delete to generate a log entry.
Of course, you would ask - "But the DBA can delete those rows". Sure they
can - but you can also log it so that you also have it generate a MSMQ
transaction where you can have a service that processes those records into
an external file store.
I would also reccommend seeing the July 2004 edition of SQL Server
Magazine - p.15 on "Privacy Matters" for some other suggestions as well.
=-Chris
"Moi" <moi@.here.com> wrote in message
news:7FF1D2C9B9DB6525.1907DEF59F04D933.8248BD072E33C3AD@.lp.airnews.net...
> Hey all,
> I am developing an application to track very sensitive company personnel
> information. This of course raised the issue of protecting this sensitive
> information from everyone not authorized to see it.....including the DB
> Administrator. The DB Server (SQL 7) has many databases on it, but the
> System Administrator must not be able to access the data in those tables
> containing the sensitive information. I have tried denying an SA user
> rights to SELECT from a table, but I could still select and view all the
> data.
> Any ideas?
> Jeremy Byron
> jbyron@.cynaptec.nb.ns.ca <remove ns to email me>
>|||> you can put a trigger on the select,
Are you sure about that? Books Online doesn't seem to agree:
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ]
1; UPDATE ] [ , ] [
DELETE ] }
http://www.aspfaq.com/
(Reverse address to reply.)

Deny Administrator Access to DB

Hey all,
I am developing an application to track very sensitive company personnel
information. This of course raised the issue of protecting this sensitive
information from everyone not authorized to see it.....including the DB
Administrator. The DB Server (SQL 7) has many databases on it, but the
System Administrator must not be able to access the data in those tables
containing the sensitive information. I have tried denying an SA user
rights to SELECT from a table, but I could still select and view all the
data.
Any ideas?
Jeremy Byron
jbyron@.cynaptec.nb.ns.ca <remove ns to email me>
In article <7FF1D2C9B9DB6525.1907DEF59F04D933.8248BD072E33C3A D@.lp.airnews.net>,
Moi <moi@.here.com> wrote:
>Hey all,
>I am developing an application to track very sensitive company personnel
>information. This of course raised the issue of protecting this sensitive
>information from everyone not authorized to see it.....including the DB
>Administrator. The DB Server (SQL 7) has many databases on it, but the
>System Administrator must not be able to access the data in those tables
>containing the sensitive information. I have tried denying an SA user
>rights to SELECT from a table, but I could still select and view all the
>data.
>
AFAIK, this is not possible. Much like the root user in UNIX, sa must and
does, have access to everything.
|||Actually there may be a way:
1. setup a username and role for yourself with the appropriate rights.
2. have someone who should have access to the information change the sa
password at the console.
The downside is that if you ever need to do something with SA privledges,
you will need to have the person who created the password give it to you so
you can get in and do whatever you have to do.
This is more secure, but no foolproof. A sly DBA will be able to setup
their role in such a way that they won't need the SA password.
Tom
Moi <moi@.here.com> wrote in message
news:7FF1D2C9B9DB6525.1907DEF59F04D933.8248BD072E3 3C3AD@.lp.airnews.net...
> Hey all,
> I am developing an application to track very sensitive company personnel
> information. This of course raised the issue of protecting this sensitive
> information from everyone not authorized to see it.....including the DB
> Administrator. The DB Server (SQL 7) has many databases on it, but the
> System Administrator must not be able to access the data in those tables
> containing the sensitive information. I have tried denying an SA user
> rights to SELECT from a table, but I could still select and view all the
> data.
> Any ideas?
> Jeremy Byron
> jbyron@.cynaptec.nb.ns.ca <remove ns to email me>
>

Deny Administrator Access to DB

Hey all,
I am developing an application to track very sensitive company personnel
information. This of course raised the issue of protecting this sensitive
information from everyone not authorized to see it.....including the DB
Administrator. The DB Server (SQL 7) has many databases on it, but the
System Administrator must not be able to access the data in those tables
containing the sensitive information. I have tried denying an SA user
rights to SELECT from a table, but I could still select and view all the
data.
Any ideas?
Jeremy Byron
jbyron@.cynaptec.nb.ns.ca <remove ns to email me>
You cannot stop SA from looking at the tables...
HOWEVER - you CAN log the users that view the data.
For the tables that contain sensitive information, you can put a trigger on
the select, update, and delete to generate a log entry.
Of course, you would ask - "But the DBA can delete those rows". Sure they
can - but you can also log it so that you also have it generate a MSMQ
transaction where you can have a service that processes those records into
an external file store.
I would also reccommend seeing the July 2004 edition of SQL Server
Magazine - p.15 on "Privacy Matters" for some other suggestions as well.
=-Chris
"Moi" <moi@.here.com> wrote in message
news:7FF1D2C9B9DB6525.1907DEF59F04D933.8248BD072E3 3C3AD@.lp.airnews.net...
> Hey all,
> I am developing an application to track very sensitive company personnel
> information. This of course raised the issue of protecting this sensitive
> information from everyone not authorized to see it.....including the DB
> Administrator. The DB Server (SQL 7) has many databases on it, but the
> System Administrator must not be able to access the data in those tables
> containing the sensitive information. I have tried denying an SA user
> rights to SELECT from a table, but I could still select and view all the
> data.
> Any ideas?
> Jeremy Byron
> jbyron@.cynaptec.nb.ns.ca <remove ns to email me>
>
|||> you can put a trigger on the select,
Are you sure about that? Books Online doesn't seem to agree:
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [
DELETE ] }
http://www.aspfaq.com/
(Reverse address to reply.)