I have developed a simple XAF application that I was looking to publish to our intranet for more extensive testing, however, as these things often go we have been asked to upgrade the bckend o sue SQL Server 2005 (everything so far has been done in SQL 2000. I have pointed all the references to the new server (Windows app: Connection string in app.config & the properties of the Sql connection in the WinApplication.vb) (Web app: Connection string in web.config & the properties of the Sql connection in the WebApplication.vb).

When I run the application connected to the old (2000) server all runs well, when I change the settings to 2005, I get the login prompt and I enter the details and click the login button, after a pause the output window in the VS IDE shows a call to UpdateDB and then I get an error in DevExpress.XPO.v8.2.dll (Unable to open the database) - The server was not found or was not accessible. What credentials does XAF use to attempt connection and creation of the database?  was going to run the Windows forms version to create the database then run the web version as the main test application. I have selected Authentification Standard and Security Complex in both cases.

Hi Steve,

XAF is not based on the database servers in any way. It appears that this problem is outside the bounds of the XAF product itself.

Can you please ensure that the connection string is set correctly and that the database server is available.

You can do this as shown in the attached screenshot or create a simple non-XAF application with XPO that will connect to the same server.

 It seems that almost every time after beating my head against a problem for hours or days and I give in and post about the problem I'm having I appear to immediately find a solution to it myself soon after the posting. Now I'm not sure why but when I comment out the connectionstring entry in the app.config file and delete and recreate the sql connection in the WinApplication.vb (Design) view it now works. I have yet to get the web bit going but that is the painful process of making sure all the libraries are copied to the webservers bin directory (it's a shame this can't be more straight forward).

One of my original questions still remains and if DevExpress don't know the answer then I am surprised (though I do appreciate that this is not an XAF specific thing)....

When my XAF application connects to a sql database that is set to use Windows NT Authentification what permissions/user am I connecting to the database with - i.e. what do I need to give permissions to within my SQL database or should I just trust XAF to build the appropriate permissions on the database?

XAF Security model

Auth Standard                                Auth Active Directory

Do these just work internally for XAF and determine whether the user is prompted for a user ID & password - with the Active directory version taking the users windows login as the value to lookup in XAFs user table?

Does this work in the same way for both Windows and Web XAF applications?

XAF Windows - when using Windows NT Authentification (on the database) does SQL server only allow (windows) users that have permissions set on the database to use the application since XAF will get read permission exceptions on the database otherwise?

XAF Web - with Windows NT Authentification (on the database) because the user is comming in remotely vai a website will XAF be accessing the database effectively as the default anonymous user setup on teh webserver and hence THIS user needs permissions to be assigned to the SQL database? If not how is the user authentification happening and what user needs access to the database objects.

Hi,

The only information that decides how to access your database is your connection string (wether from config file or embeded in WinApplication.designer.cs file (for win application and the same goes for web application in its respective file).

So, if your connection string states a Windows authentication mode, then database should give access to the windows account used to log on and this has nothing to do with XAF authentication which could be standard using a user name and password stored in XAF database (user table or a custom table).

If your connection string is a SQL based connection meaning that you provide a user and a password, then your database should have that sql user defined and XAF authentication mode here also does not matter.

Hope this helps,

Mohsen

good questions, an area i have been happy to stay away from. Especially for web apps, i *only* use a special account i create within the db itself to control access and use that account within my connection string. I do not want any connection (pardon the pun) between my apps and the box it may or may not sit on. This allows very fine-grained and more importantly independent security. I am perhaps lucky (or unlucky depending on your vantage point) to not work within larger organizations that demand Active Directory type access.

Anywhoo, just thought i would pipe in that by sticking exclusively to sql server logins, you can avoid most of this headache, but i am always willing to listen to reasons why this may not be the best approach.

cheers, drew..

This would be my prefered solution also BUT Microsoft (for their own gains no doubt) has been "encouraging" users of SQL server to use Active Directory to validate users when the make a connection to a database. For "normal" dot net development this is fine and allows me to control things pretty much in the same manner as using SQL security. However, when it comes to using an application framework (XAF) the waters become somewhat muddied.... For a straight Windows project I can see that authentification can continue as per a "nomal" dot net application since the application is (most probably) running in the domain and the user runnng it has an Active Directory account and can be validated in the normal way.

Via the web however....

If I have "Authenticfication Standard" this merely checks with the XAF user data to check the user should be allowed to access the application (seurity simple) or elements of the application (security complex). The connection to underlying database must surely be made using the anonymous IIS user?

If I have "Authentification ActiveDirectory" for a web application does/should the application prompt the user with a windows domain logon or should it also use the IUSER anonymous access?

Of course if it is possible to modify an SQL2000 & SQL2005 installation that was configured for Active directory authentification to also use SQL server authentification I could possibly bypass my clients "usual" security arrangements and stick with SQL server validation of users.... Is it possible (I know there is a mixed mode of operation but I thought this was something that was setup on installation of the server software.... Is it possible to do on a database by database basis?

If you have no choice other than use ActiveDirectory authentication in your database, my guess is that you have to make sure that your web process runs under your active directory user by making sure that you selected the right authentication mode in your configuration (web.config and/or IIS) with no impersonating for instance.

Once you have that configuration in place and your SQL server configured for Active Directory, then it should work.

Mohsen

nb: You can only change the security mode (SQL or Mixed) at Server level and not database.