kkinsella
Posts: 4
Joined: Thu Jun 14, 2012 12:30 pm

SQL Server Windows Authentication Issues

Thu Jun 14, 2012 12:43 pm

Hello I am currently attempting a small project to create an installer to add IIS files to a Windows 2008R2 server and a SQL database to a Windows2008R2 /SQL2008R2 server instance. version of AI = 7.6.1

Our network requires the use of Windows/Domain based accounts for all SQL connectivity, as such SQL server only logins are disabled and the SQL server is running in Windows Authentication Mode only. Herein lies my issue, I cannot get the AI installer to connect to the SQL server when I choose to use "Trusted Connection" in the Authentication seciton of the Database server connection page. The default [proprty] applied to the trusted connection is [Logonuser] whihc only captures the username of the person logged on, not the domain as well, thus when it is passed to the SQL server it is denied access as it is not a valid user. i.e the user expected is Domain\Username as is loaded into SQL as an administrator, but the credentials passed are Username.
Oddly though this error only presents itself when attempting to do something with the need for Sysadmin rights on the SQL server, e.g create a database or SQL Agent jobs. if the Database already exists the system seems to like the connection and allow it to perform amemndemnts to it.

Are you abel to advise is there something silly I am missing or have configured incorrectly?

Many Thanks

Kevin

mihai.petcu
Posts: 3860
Joined: Thu Aug 05, 2010 8:01 am

Re: SQL Server Windows Authentication Issues

Fri Jun 15, 2012 9:36 am

Hi Kevin and welcome to Advanced Installer forums,

You can try to use "Impersonate installing user" option from SQL Scripts page connections list context menu.

All the best,
Mihai
Mihai Petcu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

kkinsella
Posts: 4
Joined: Thu Jun 14, 2012 12:30 pm

Re: SQL Server Windows Authentication Issues

Fri Jun 15, 2012 9:46 am

Hi there thanks for the suggestion, howeverI do not have the ability to choose the option to "Impersonate Installing user" it is greyed out and non selectable.

are you able to assiste further as to why this might be?

kind regards Kevin

mihai.petcu
Posts: 3860
Joined: Thu Aug 05, 2010 8:01 am

Re: SQL Server Windows Authentication Issues

Fri Jun 15, 2012 12:22 pm

Hi Kevin,

If you're using an older version you can try to select "Database server" and then right click on it. Its context menu should have the option available.

All the best,
Mihai
Mihai Petcu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

kkinsella
Posts: 4
Joined: Thu Jun 14, 2012 12:30 pm

Re: SQL Server Windows Authentication Issues

Fri Jun 15, 2012 1:18 pm

Excellent thank you that worked, :D however it now provides me wiht a further set of issues which i am hoping you may be able to help with.

i would like to provide a test button next to the SQKL server and dataabse box on the UI . but when following the examples on the AI website it only seems to reference a database connection using sql authentication, eg it specifies th euid and password that will be supplied as coming from to field variables on the UI, however as i am usign wiondows authentication this wont work, i have tried amendign the string for the driver but it doenst do anythign the button just clicks and nothing happens.

also now that my sql scripts install the database correclty,(again thanks for the help wiht this) i need to apply a user to the newly created database to allow for acccess to it, this user needs to be the machine domain account name of the iis server i have installed the applicaition to e.g. domain\server$ is there a way i can grab this information during the install process and apply it as a variable to a sql script?

kind regards Kevin

mihai.petcu
Posts: 3860
Joined: Thu Aug 05, 2010 8:01 am

Re: SQL Server Windows Authentication Issues

Mon Jun 18, 2012 9:56 am

Hi Kevin,

Here's the How do I set the authorization type of an SQL connection? article to help you with setting the authorization type in the connection string.
To set a property in the script you can use the "Script Replacements" feature.

All the best,
Mihai
Mihai Petcu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Return to “Common Problems”