SmartAssembly + SQL + Domain Controller + Windows Auth = :[]

Ognyandim

Hello,
We have 1 TFS Build machine and 3 developer machines. I have installed SA Pro on the Build and logged in with Windows Authentication but with Administrator ( which was used to install the SQL instance ). All tables are created and everything is fine. But now I am installing SA on developers machines and they do not log in as Administrator and SA tells me that it "Could not connect to SQL server".

There is an excerpt at the end of the page on the first link for SQL authentication. I used it to try to connect with 'sa' and it works fine, but I can not put the 'sa' ( Server Administrator ) password in this plain xml file used for SA configuration.

From this excerpt I see two connections - the first for SA database and the second for 'master'.

I am at the conclusion that the regular domain user ( windows authentication ) needs privileges on both the SA and master tables ( as SQL auth. excerpt points that ) that I did not give.

My question is : What privileges do I need to set for a regular domain user account for both tables in order to make the Windows Authentication work?

More general : What privileges needs any type of account in order to user the SQL reporting

I have read this
http://www.red-gate.com/supportcenter/C ... 210448.htm
and this
http://www.edsquared.com/2011/12/27/Int ... Build.aspx
but did not find any information regarding what privileges are required for Windows Authentication. The tutorial on the first page is not very useful in my case.
I read a ton of things for four days now for SQL ports and protocols and etc. on the net before posting here.

Thanks a lot for any help. I will appreciate it really.

Brian Donahue

There are no special permissions required and setup for Windows users would be the same as SQL users.

The thing that causes problems is that when you initially configure SmartAssembly for SQL, it tries to create the database, and the error message just says "login failed" when it really should say CREATE DATABASE rights are required.

So in addition to db_datawriter that you need to put data into the tables during normal operation, you will need CREATE DATABASE and permission to create schema objects initially.

Ognyandim

Brian Donahue wrote:

The thing that causes problems is that when you initially configure SmartAssembly for SQL, it tries to create the database, and the error message just says "login failed" when it really should say CREATE DATABASE rights are required.

So in addition to db_datawriter that you need to put data into the tables during normal operation, you will need CREATE DATABASE and permission to create schema objects initially.

Thanks for the quick response.

I have logged in once and the database and all of its tables are created. But that initial log-in was with Administrator. Now I need to set things up for regular accounts. The regular account already is db_datawriter.

If I can log in with 'sa' and do whatever and then nothing with my regular DC account there must be a difference between the privileges that blocks me. Which differences are important for the proper configuration of SmartAssembly.

I want to point out that at the beginning I do not have any user in the SQL server defined. It is defined only in the Active Directory. I must add it manually in the SQL Server instance and give it all the required privileges because everything is not allowed by default.

Thanks for your support.

Brian Donahue

You can add the Windows user to SQL Server using SSMS (under security) and then grant it access to the SmartAssembly database. Then add it to db_datawriter.

Ognyandim

I made a SQL user named SmartAssembly and granted it the privileges you said I should. Now it works.

The problem with the Windows Authentication user probably is in the configuration of the firewalls of the domain controller which I will solve later. I believe so because my virtual pcs are in different network then the DC and the DC is still not configured to handle this.

Thanks for your support.

Brian Donahue

Good point - the SQL Server needs to get into AD to resolve Windows account SIDs and things like that. So you need a solid LDAP connection between the two and you have to ensure the SQL Server Service account can poke around AD. I think be default all accounts (even the builtin computer accounts) have the rights.