Yes - there's no question about that and I don't mean to imply otherwise (though likely I did).
I am currently opposed to that model of db deployment, though, so I am not going to be much of a candidate :-) I'm sure others have other opinions on the topic though. Perhaps I could be swayed. I also don't really buy in to the code-first methodology for databases either. I'm a tad of a purist I guess, haha. -----Original Message----- From: Rob Mensching [mailto:r...@firegiant.com] Sent: Thursday, March 13, 2014 10:46 AM To: General discussion about the WiX toolset. Subject: Re: [WiX-users] Conditional SSPI / SQL Authentication on SqlScript Note: the SQL Custom Actions in the WiX toolset could be improved if someone made an effort to work on them directly. Until then the performance problems will remain. _______________________________________________________________ FireGiant | Dedicated support for the WiX toolset | http://www.firegiant.com/ -----Original Message----- From: Bryan Wolf [mailto:brw...@jackhenry.com] Sent: Thursday, March 13, 2014 8:32 AM To: General discussion about the WiX toolset. Subject: Re: [WiX-users] Conditional SSPI / SQL Authentication on SqlScript Well, the approach we take is packaging SSDT in the installer and shipping it around. The sqlpackage.exe binary is quite small and has only a couple of dependencies. If this is a gray area for you, tbh I would consider putting the SSDT downloads as a part of your deployment package in some way. You can't really point to Microsoft's servers I believe, but re-hosting a download or making them a dependency definitely seems within the license. We ship the following dependencies Microsoft.Data.Tools.Schema.Sql.dll Microsoft.Data.Tools.Utilities.dll Microsoft.SqlServer.Dac.dll Microsoft.SqlServer.TransactSql.ScriptDom.dll SqlPackage.exe And then you'll want to get the master and mdb dacpacs for each version of supported VS from your build or development servers: http://social.msdn.microsoft.com/forums/sqlserver/en-US/53fa04eb-aa6d-4eb9-ab4d-0f27b55d28e7/msbuild-and-master-dacpac Include all that in the binary table with a nice CA to extract everything and bob's your uncle! :-) But every environment is going to look differently upon that. Regardless, SSDT is probably a worthy venture to solve - especially if your scope of supported versions of VS are small. Thanks, Bryan -----Original Message----- From: Levi Wilson [mailto:l...@leviwilson.com] Sent: Thursday, March 13, 2014 10:13 AM To: General discussion about the WiX toolset. Subject: Re: [WiX-users] Conditional SSPI / SQL Authentication on SqlScript Bryan, Thanks for your response. This is definitely what it is starting to feel like. Awhile back John had mentioned that this is how he does his database deployments. My installer may be ran on a server that doesn't have SSDT. Do you support this in your installers and if so, what pattern do you use to use SqlPackage.exe on a machine that may not have it? Levi On Thu, Mar 13, 2014 at 10:57 AM, Bryan Wolf <brw...@jackhenry.com> wrote: > The core of this issue might be that you're trying to create the database. > > But while the WiX SQL Custom Actions are good for one-offs like what > John wrote, I think you'll find them not adequate for modern day > database apps - especially in the creation world - unless you're > creating some type of super simplistic database. You're probably going > to be happier using a sqldatabase project and integrating your SQL as > a script with DB Rollback and transactions plus batch execution. > SQLPackage is already slow enough, but SQL String seems to get really > bogged down after more than 100 or so scripts, which in a decent sized > database shows up fast. > > At any rate, you can definitely modify the in-memory database. I've > done it - not proud of it - but you have to make sure your custom > action re-does that every time you launch. What you practically cannot > do is save the in-memory database (you technically can but it's not > going to be the one that gets cached unless you write a CA to update cache > lists). > > Regardless, even if you can't get much developer buy-in it's probably > going to be way less effort for someone to learn SQL Server, learn how > sql databases work, learn how SQLProj works, and then build the > sqlcmdvar interface and any CAs to populate them than it is going to > be to get anything more than trivial database deployed in a maintainable > fashion. > > (Not to say SQLString is the worst thing ever - it's great for ad-hoc > things like running a single sp or something; it's just not a suitable > database deployment tool) > > -----Original Message----- > From: Wesley Manning [mailto:wmann...@dynagen.ca] > Sent: Thursday, March 13, 2014 9:08 AM > To: General discussion about the WiX toolset. > Subject: Re: [WiX-users] Conditional SSPI / SQL Authentication on > SqlScript > > Yes you're correct. I missed that part. > > -----Original Message----- > From: Levi Wilson [mailto:l...@leviwilson.com] > Sent: March-13-14 10:33 AM > To: General discussion about the WiX toolset. > Subject: Re: [WiX-users] Conditional SSPI / SQL Authentication on > SqlScript > > You can add to the MSI database, but as near as I can tell you cannot > UPDATE the MSI database :( > > > On Thu, Mar 13, 2014 at 9:09 AM, Wesley Manning <wmann...@dynagen.ca> > wrote: > > > Actually you can modify the MSI at runtime, at least its database. > > Don't know if you can modify the embedded files. Semi-custom action: > > http://www.joyofsetup.com/2007/07/01/semi-custom-actions/. > > > > -----Original Message----- > > From: Levi Wilson [mailto:l...@leviwilson.com] > > Sent: March-12-14 7:19 PM > > To: General discussion about the WiX toolset. > > Subject: Re: [WiX-users] Conditional SSPI / SQL Authentication on > > SqlScript > > > > I tried this example; yours has the @User set on the <SqlString /> > > rather than the <SqlDatabase />. I tried this, so I removed the > > @User from the SqlDatabase and added it to SqlScript instead...this > > looks like it ONLY uses Integrated Security (because of the absence > > of it on the <SqlDatabase /> and NEVER uses the SQL Authentication. > > > > This is aggravating that you cannot use something like > > @User="[SOME_USER_PROPERTY]" and then have it evaluate it then :(. I > > feel like I HAVE to be missing something. > > > > The OTHER fix I can think of is to update the "User_" field in the > > SqlDatabase table...but it doesn't appear that you can modify the > > MSI at runtime. > > > > > > > > On Wed, Mar 12, 2014 at 5:43 PM, John Cooper > > <jocoo...@jackhenry.com> > > wrote: > > > > > Well, I had only one product that deploys using sql:SqlDatabase, > > > and it has recently converted to SqlPackage. Here's a sampling of > > > the code I use which has been tested under both Windows and SQL > > > Authentication on several products for over two years: > > > > > > <Fragment> > > > <PropertyRef Id="SQL_SERVERNAME" /> > > > <PropertyRef Id="SQL_USERNAME" /> > > > <PropertyRef Id="SQL_PASSWORD" /> > > > > > > <util:User Id="DbUser" Name="[SQL_USERNAME]" > > Password="[SQL_PASSWORD]" > > > /> > > > <sql:SqlDatabase Id="DbProduct" Database="Product" > > > Server="[SQL_SERVERNAME]" /> > > > </Fragment> > > > <Fragment> > > > <PropertyRef Id="SQL_DEPLOY_DATABASE" /> > > > <PropertyRef Id="SQL_RECOVERY_SIMPLE" /> > > > > > > <DirectoryRef Id="INSTALLLOCATION"> > > > <Component Id="CmpProductRecoveryFull" Guid="PUT-GUID-HERE" > > > KeyPath="yes"> > > > <Condition><![CDATA[NOT SQL_RECOVERY_SIMPLE AND > > > SQL_DEPLOY_DATABASE]]></Condition> > > > > > > <sql:SqlString Id="SqlRecoveryAsFull" ExecuteOnInstall="yes" > > > SqlDb="DbProduct" User="DbUser" SQL="ALTER DATABASE Product SET > > > RECOVERY FULL"/> > > > </Component> > > > </DirectoryRef> > > > </Fragment> > > > > > > -- > > > John Merryweather Cooper > > > Build & Install Engineer - ESA > > > Jack Henry & Associates, Inc.(r) > > > Shawnee Mission, KS 66227 > > > Office: 913-341-3434 x791011 > > > jocoo...@jackhenry.com > > > www.jackhenry.com > > > > > > > > > > > > -----Original Message----- > > > From: Levi Wilson [mailto:l...@leviwilson.com] > > > Sent: Wednesday, March 12, 2014 4:03 PM > > > To: General discussion about the WiX toolset. > > > Subject: Re: [WiX-users] Conditional SSPI / SQL Authentication on > > > SqlScript > > > > > > This leads me to believe that I should be able to do this? > > > > > > http://sourceforge.net/p/wix/bugs/626/ > > > > > > > > > On Wed, Mar 12, 2014 at 4:19 PM, Levi Wilson <l...@leviwilson.com> > > wrote: > > > > > > > John, how were you able to accomplish this? I tried to > > > > explicitly do this like so: > > > > > > > > <Property Id="EMPTY_USER" /> > > > > <Property Id="EMPTY_PASSWORD" /> > > > > > > > > > > > > <util:User Id='SqlInstallerUserId' > > > > > > > > Name='[EMPTY_USER]' > > > > > > > > Password='[EMPTY_PASSWORD]' /> > > > > > > > > > > > > Doing so yields "ExecuteSqlStrings: Error 0x80040e4d: failed to > > > > connect > > > to database: '<my database>'" in the output log, and the > > > EMPTY_USER and EMPTY_PASSWORD do not even show up (because they were > > > never set). > > > > > > > > > > > > I'm not sure how to get them to evaluate to empty, but I thought > > > > that > > > would suffice. Since it did not, then I tried this: > > > > > > > > > > > > > > > > <Property Id="EMPTY_USER" Value="something" /> <Property > > > > Id="EMPTY_PASSWORD" Value="something" /> > > > > > > > > <SetProperty Id="EMPTY_USER" After="AppSearch" Value="" /> > > > > > > > > <SetProperty Id="EMPTY_PASSWORD" After="AppSearch" Value="" /> > > > > > > > > > > > > In the output log, I see the original property values, then when > > > > it sets > > > them to empty it says it's "Deleting" the properties because their > > > values are empty, and I still get the same error: > > > > > > > > > > > > ExecuteSqlStrings: Error 0x80040e4d: failed to connect to database: > > > '<my database>' > > > > > > > > > > > > I have a feeling I'm missing something simple. > > > > > > > > > > > > Additionally, I thought I would be able to set the "User_" > > > > column of the > > > "SqlDatabase" table to a NULL to try to get that to happen, but > > > apparently you can't modify a value in the MSI database at runtime? > > > > > > > > > > > > Thanks in advance, > > > > > > > > > > > > Levi > > > > > > > > > > > > > > > > > > > > > > > > On Tue, Mar 11, 2014 at 11:42 AM, John Cooper > > > ><jocoo...@jackhenry.com > > > >wrote: > > > > > > > >> Yes. In your case, if SQL_ADMIN_USERNAME and > > > >> SQL_ADMIN_PASSWORD both evaluate to empty-string, then the > > > >> sql:SqlDatabase will deploy with Windows Authentication. > > > >> > > > >> I use this pattern all the time. > > > >> > > > >> -- > > > >> John Merryweather Cooper > > > >> Build & Install Engineer - ESA > > > >> Jack Henry & Associates, Inc.(r) Shawnee Mission, KS 66227 > > > >> Office: 913-341-3434 x791011 > > > >> jocoo...@jackhenry.com > > > >> www.jackhenry.com > > > >> > > > >> > > > >> > > > >> -----Original Message----- > > > >> From: Levi Wilson [mailto:l...@leviwilson.com] > > > >> Sent: Tuesday, March 11, 2014 10:33 AM > > > >> To: wix-users@lists.sourceforge.net > > > >> Subject: [WiX-users] Conditional SSPI / SQL Authentication on > > > >> SqlScript > > > >> > > > >> I have a component that has multiple <SqlScript /> elements. In > > > >> my dialog sequence I allow the user to choose whether or not > > > >> they want to use SQL or Windows Authentication for the connection. > > > >> > > > >> Each <SqlScript /> references my <SqlDatabase /> that currently > > > >> looks like > > > >> this: > > > >> > > > >> <util:User Id='SqlInstallerUserId' > > > >> Name='[SQL_ADMIN_USERNAME]' > > > >> Password='[SQL_ADMIN_PASSWORD]' /> > > > >> > > > >> <sql:SqlDatabase Id="CompassFrameworkDatabase" > > > >> Server="[SQL_SERVER]" Database="[SQL_DATABASE]" > > > >> User="SqlInstallerUserId" /> > > > >> > > > >> The problem is that will never work with Windows Authentication > > > >> :-) > > > >> > > > >> I've seen some have two different components that specified > > > >> different <SqlDatabase /> elements, but I would rather not do that. > > > >> > > > >> Is there a way to basically "clear" the "User_" record in the > > > >> SqlDatabase table in the MSI to force it to use Windows > > > >> Authentication depending on what the user chose in the dialog? > > > >> > > > >> Thanks in advance. > > > >> > > > >> Levi > > > >> > > > >> --------------------------------------------------------------- > > > >> -- > > > >> -- > > > >> -- > > > >> --------- Learn Graph Databases - Download FREE O'Reilly Book > > > >> "Graph Databases" is the definitive new guide to graph > > > >> databases and their applications. Written by three acclaimed > > > >> leaders in the field, this first edition is now available. > > > >> Download your free book > > today! > > > >> http://p.sf.net/sfu/13534_NeoTech > > > >> _______________________________________________ > > > >> WiX-users mailing list > > > >> WiX-users@lists.sourceforge.net > > > >> https://lists.sourceforge.net/lists/listinfo/wix-users > > > >> NOTICE: This electronic mail message and any files transmitted > > > >> with it are intended exclusively for the individual or entity > > > >> to which it is addressed. The message, together with any > > > >> attachment, may contain confidential and/or privileged information. > > > >> Any unauthorized review, use, printing, saving, copying, > > > >> disclosure or distribution is strictly prohibited. If you have > > > >> received this message in error, please immediately advise the > > > >> sender by reply email and delete all copies. > > > >> > > > >> > > > >> > > > >> --------------------------------------------------------------- > > > >> -- > > > >> -- > > > >> -- > > > >> --------- Learn Graph Databases - Download FREE O'Reilly Book > > > >> "Graph Databases" is the definitive new guide to graph > > > >> databases and their applications. Written by three acclaimed > > > >> leaders in the field, this first edition is now available. > > > >> Download your free book > > today! > > > >> http://p.sf.net/sfu/13534_NeoTech > > > >> _______________________________________________ > > > >> WiX-users mailing list > > > >> WiX-users@lists.sourceforge.net > > > >> https://lists.sourceforge.net/lists/listinfo/wix-users > > > >> > > > > > > > > > > > > > > ------------------------------------------------------------------ > > > -- > > > -- > > > -------- Learn Graph Databases - Download FREE O'Reilly Book > > > "Graph Databases" is the definitive new guide to graph databases > > > and their applications. Written by three acclaimed leaders in the > > > field, this first edition is now available. Download your free book today! > > > http://p.sf.net/sfu/13534_NeoTech > > > _______________________________________________ > > > WiX-users mailing list > > > WiX-users@lists.sourceforge.net > > > https://lists.sourceforge.net/lists/listinfo/wix-users > > > NOTICE: This electronic mail message and any files transmitted > > > with it are intended exclusively for the individual or entity to > > > which it is addressed. The message, together with any attachment, > > > may contain confidential and/or privileged information. > > > Any unauthorized review, use, printing, saving, copying, > > > disclosure or distribution is strictly prohibited. If you have > > > received this message in error, please immediately advise the > > > sender by reply email and delete all copies. > > > > > > > > > > > > ------------------------------------------------------------------ > > > -- > > > -- > > > -------- Learn Graph Databases - Download FREE O'Reilly Book > > > "Graph Databases" is the definitive new guide to graph databases > > > and their applications. Written by three acclaimed leaders in the > > > field, this first edition is now available. Download your free book today! > > > http://p.sf.net/sfu/13534_NeoTech > > > _______________________________________________ > > > WiX-users mailing list > > > WiX-users@lists.sourceforge.net > > > https://lists.sourceforge.net/lists/listinfo/wix-users > > > > > > > -------------------------------------------------------------------- > > -- > > -------- Learn Graph Databases - Download FREE O'Reilly Book "Graph > > Databases" is the definitive new guide to graph databases and their > > applications. Written by three acclaimed leaders in the field, this > > first edition is now available. Download your free book today! > > http://p.sf.net/sfu/13534_NeoTech > > _______________________________________________ > > WiX-users mailing list > > WiX-users@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/wix-users > > > > > > -------------------------------------------------------------------- > > -- > > -------- Learn Graph Databases - Download FREE O'Reilly Book "Graph > > Databases" is the definitive new guide to graph databases and their > > applications. Written by three acclaimed leaders in the field, this > > first edition is now available. Download your free book today! > > http://p.sf.net/sfu/13534_NeoTech > > _______________________________________________ > > WiX-users mailing list > > WiX-users@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/wix-users > > > > ---------------------------------------------------------------------- > -------- Learn Graph Databases - Download FREE O'Reilly Book "Graph > Databases" is the definitive new guide to graph databases and their > applications. Written by three acclaimed leaders in the field, this > first edition is now available. Download your free book today! > http://p.sf.net/sfu/13534_NeoTech > _______________________________________________ > WiX-users mailing list > WiX-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/wix-users > > > ---------------------------------------------------------------------- > -------- Learn Graph Databases - Download FREE O'Reilly Book "Graph > Databases" is the definitive new guide to graph databases and their > applications. Written by three acclaimed leaders in the field, this > first edition is now available. Download your free book today! > http://p.sf.net/sfu/13534_NeoTech > _______________________________________________ > WiX-users mailing list > WiX-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/wix-users > NOTICE: This electronic mail message and any files transmitted with it > are intended exclusively for the individual or entity to which it is > addressed. The message, together with any attachment, may contain > confidential and/or privileged information. > Any unauthorized review, use, printing, saving, copying, disclosure or > distribution is strictly prohibited. If you have received this message > in error, please immediately advise the sender by reply email and > delete all copies. > > > > ---------------------------------------------------------------------- > -------- Learn Graph Databases - Download FREE O'Reilly Book "Graph > Databases" is the definitive new guide to graph databases and their > applications. Written by three acclaimed leaders in the field, this > first edition is now available. Download your free book today! > http://p.sf.net/sfu/13534_NeoTech > _______________________________________________ > WiX-users mailing list > WiX-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/wix-users > ------------------------------------------------------------------------------ Learn Graph Databases - Download FREE O'Reilly Book "Graph Databases" is the definitive new guide to graph databases and their applications. Written by three acclaimed leaders in the field, this first edition is now available. Download your free book today! http://p.sf.net/sfu/13534_NeoTech _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users NOTICE: This electronic mail message and any files transmitted with it are intended exclusively for the individual or entity to which it is addressed. The message, together with any attachment, may contain confidential and/or privileged information. Any unauthorized review, use, printing, saving, copying, disclosure or distribution is strictly prohibited. If you have received this message in error, please immediately advise the sender by reply email and delete all copies. ------------------------------------------------------------------------------ Learn Graph Databases - Download FREE O'Reilly Book "Graph Databases" is the definitive new guide to graph databases and their applications. Written by three acclaimed leaders in the field, this first edition is now available. Download your free book today! http://p.sf.net/sfu/13534_NeoTech _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users ------------------------------------------------------------------------------ Learn Graph Databases - Download FREE O'Reilly Book "Graph Databases" is the definitive new guide to graph databases and their applications. Written by three acclaimed leaders in the field, this first edition is now available. Download your free book today! http://p.sf.net/sfu/13534_NeoTech _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users NOTICE: This electronic mail message and any files transmitted with it are intended exclusively for the individual or entity to which it is addressed. The message, together with any attachment, may contain confidential and/or privileged information. Any unauthorized review, use, printing, saving, copying, disclosure or distribution is strictly prohibited. If you have received this message in error, please immediately advise the sender by reply email and delete all copies. ------------------------------------------------------------------------------ Learn Graph Databases - Download FREE O'Reilly Book "Graph Databases" is the definitive new guide to graph databases and their applications. Written by three acclaimed leaders in the field, this first edition is now available. Download your free book today! http://p.sf.net/sfu/13534_NeoTech _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users
