Hey All,

I'm having trouble getting umbraco to recognize my Microsoft SQL Server 2005.

The server is bundeled with Visual Studio 2005 and it would seem silly to uninstall it only to install MSDE 2000.


for the umbracoDbDSN key I have tried the following:
SQLEXPRESS
127.0.0.1
.
localhost
9.0.1399 (number in the title of the server from Management Studio)

etc.

but on the wizard page I always get
Your database has been found and is identified as: Unavailable


I'm using Microsoft SQL Server Management Studio Express as my SQL manager, and I think I have set it up properly, I even enabled a compatibility level: SQL Server 2000 setting hoping that would get things going with no luck.

I have created the database successfully, executed the appropriate sql quieries for the clean umbraco database, and added an UmbracoUser to the Security Login with db_owner, db_datareader, and db_datawriter permissions for the umbraco database.

Thanks for any help!


P.S.

Apparently the prefered method for connecting to a SQL server in .NET Framework version 2.0 is connectionStrings Element (ASP.NET Settings Schema)
<a href="http://msdn2.microsoft.com/en-us/library/bf7sd233.aspx">http://msdn2.microsoft.com/en-us/library/bf7sd233.aspx</a>
which can be seen under Management Studio properties of database > ASP.NET tab > Configuration

maybe there is some conflict hapening here or a clue to how to tape this thing together?

I looked up information about the instance name but dont know how to use it -
http://msdn2.microsoft.com/en-us/library/ms143744.aspx

How do I add an instance name to the server name on the umbracoDbDSN key? or is the server name the instance name?

I.E. I have used (see below for origin of names):

Server=MSSQL.1;
Server=MSSQL;
Server=MSSQLSERVER;
Server=SQLExpress;
Server=SQLEXPRESS;
Server=127.0.0.1\SQLEXPRESS;
Server=.\SQLEXPRESS;
Server=localhost\SQLEXPRESS;
Server=127.0.0.1\SQLExpress;
Server=.\SQLExpress;
Server=localhost\SQLExpress;
Server=127.0.0.1\MSSQLSERVER;
Server=.\MSSQLSERVER;
Server=localhost\MSSQLSERVER;


like this

<add key="umbracoDbDSN" value="Server=MSSQL.1;Database=test;User ID=UmbracoUser;Password=UmbracoPwd;Trusted_Connection=False"/>


On SQL Server install I would have just used the default name, which would make it MSSQLSERVER or SQLExpress.

The documentation is a little unclear:

"Use the Instance Name page of the Microsoft SQL Server Installation Wizard to specify whether to create a default instance or a named instance of SQL Server Express. SQL Server Express always installs a named instance (SQLExpress) unless you select a default instance. This behavior differs from SQL Server 2005, which installs a default instance unless a named instance is selected."

vs. later on the same page:

"The default instance name is MSSQLSERVER; it does not require a client to specify the name of the instance to make a connection."

I also tried using the Instance ID given in the SQL Server properties from within SQL Server Configuration Manager which is MSSQL.1


Any ideas? What else could be wrong?

Did you authorise this DB user to access this database?
You can test it by logging in in your database with those credentials.

Then try this key:

<add key="umbracoDbDSN" value="Data Source=SERVERNAME;Initial Catalog=DBNAME;User ID=USERNAME;Password=PWD"/>
the dbname for my SQLServer 2005 Express is here MYWEBSERVER\SQLEXPRESS

Ok, getting a bit farther, it seems to be a problem of loging onto SQLEXPRESS, I can't login under SQL Server Authentication.

I get:

Login failed for user 'UmbracoUser'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

It would be nice to solve this problem if more SQLEXPRESS newbies like me start appearing :~)


Here is the sequence I use to create the database and the user:

connect to SQLEXPRESS using windows authentication

on the Databases folder, right click and select "new database"
set database name: umbracodemo
owner: <default>
click ok

on the Security/Logins folder, right click and select "new login"
under General tab
set login name: UmbracoUser
select SQL Server Authentication
set password and confirm password: UmbracoPwd
deselect Enforce password policy
set default database: master (I also tried umbracodemo here with the same result)
under User Mapping tab
select the checkbox to select umbracodemo database mapped to this user
under database roll membership for umbracodemo panel, select db_datareader, db_datawriter, db_owner, and public
click ok

I then disconnect from the Object explorer, then try connecting again with SQL Server Authentication and the user and password as above and get "failed connection"

Obviously I am not a proficient MSSQL user. Any help? It shouldnt be that hard...

@Brian -> Have you enabled SQL Server Authentication? It's disabled by default...
If you haven't done it already, this is how you do:
1. Start SQL Server Management Studio Express
2. Select Security
3. In the Server Authentication section make sure you have marked SQL Server and Windows Authentication Mode.
4. Restart the SQL Server Service

If that doesn't make any difference check your logs files after a failed login attempt. They're located here by default:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

Look in the LOG file for anything like:
2006-08-16 09:31:16.33 Logon Error: 18456, Severity: 14, State: 8.

"State: 8" indicates that there's something wrong with a User Password, I had this one due to a whitespace after the password when i first wrote it ; )

Here's a list of these different state meassages in the SQL Server LOG

Good luck!

---

" - Yeah I'd like to share your point of view, as long as it's my view too... (http://www.d-a-d.dk/lyrics/pointofview)

Success! Summary follows, anyone willing to host/edit the solution for newbies like me?


Ok, Thanks to everyone for the help. Kalle Wibeck had the key: hidden away in the properties of the SQL server instance is a security setting to enable SQL Server Authentication. It would be nice if someone was willing to read over the summary instructions below and see if there are any major #%!-ups here. That done, it would be nice to post it somewhere more permanent and easy to find...


Summary:

This guide will help in setting up the Express edition of Microsoft SQL Server 2005 for use with a web based cms system (here we are using Umbraco) on Windows XP, by Brian Dickens with super thanks to Kalle Wibeck and others on http://forums.umbraco.org (the important installation point is number 15. Enable SQL Server Authentication)


Problem description:

Login/Connection problem using the Express edition of Microsoft SQL Server 2005 (SQLEXPRESS) with the following error message from the login attempt (Umbraco install wizard):

Login failed for user 'UmbracoUser'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)


Pre-requisites:

The Express edition of Microsoft SQL Server 2005 (SQLEXPRESS) is a bundled install with Visual Studio 2005 so many people will have this SQL Server by default. It is also free to download.

The required management tool (free) -- Microsoft SQL Server Management Studio Express as well as SQLEXPRESS can be found here:

http://msdn.microsoft.com/vstudio/express/sql/download/

After installing both the server and the management tool, follow the below setup sequence in order to create a database and enable a web based cms system (Umbraco) to communicate with it.


Setup:

Using Microsoft SQL Server Management Studio Express

1. Connect to SQLEXPRESS using windows authentication

2. On the Databases folder, right click and select &quot;new database&quot;
3. Set database name: umbracodemo (or whatever)
4. Owner: &lt;default&gt;
5. Click ok

6. On the Security/Logins folder, right click and select &quot;new login&quot;

Under General tab

7. Set login name: UmbracoUser (or whatever)
8. Select SQL Server Authentication
9. Set password and confirm password: UmbracoPwd (or whatever)
10. Deselect Enforce password policy (someone wize might intervene here, but this is my settings)
11. Set default database: master

Under User Mapping tab

12. Select the checkbox to select umbracodemo database mapped to this user
13. Under database roll membership for umbracodemo panel, select db_datareader, db_datawriter, db_owner, and public

14. Click ok


Enable SQL Server Authentication, it's disabled by default...

15. Right click the root node of your SQLEXPRESS server (parent of Databases, Security, etc) and select Properties...
16. Select Security
17. In the Server Authentication section make sure you have marked SQL Server and Windows Authentication Mode.
18. Restart the SQL Server Service by right clicking the root node again and selecting restart

19. Test the setup: File &gt; disconnect from Object explorer, then File &gt; connect Object explorer, this time login with SQL Server Authentication and the user and password as above, you should be able to login, if not see Trouble shooting below.

If you have been having problems with login before, I would also highly suggest that you restart the IIS server at this point too. (at least before running the Umbraso websiteWizard.umb)


Umbraco setup:

Update the connection string in your web.config file like so --

&lt;add key=&quot;umbracoDbDSN&quot; value=&quot;Server=.\SQLEXPRESS;Database=dbname;User ID=uid;Password=pwd;Trusted_Connection=False&quot;&gt;

In the above connection string, SQLEXPRESS is the default instance name for the Express SQL Server. It is possible to name the instance during install, so if the above string doesn't work, check the name of the instance of your sql server using Microsoft SQL Server Management Studio Express. The root node in the Object Explorer tree shows the instance name. Usually it will be written IISService\SQLExpressInstanceName

Also, in the above connection string, the dot (.) simply refers to the current web server hosting the cms, i.e. your iis server, so just change the SQLExpressInstanceName unless your sql server is on a different machine that the iis server.


At this point you should follow the normal Umbraco install instructions:
http://en.wikibooks.org/wiki/Umbraco/Installation/Installing_Umbraco_on_Windows_XP_Professional#Install_Umbraco_2.1.1


If you get &quot;missing object&quot; error pages when trying to view the pages created by the websiteWizard.umb (an example website by the Umbraco folks) then the solution I found was to uninstall everything; database, website, and restart the iis server, restart the database, and install again, second times the charm :~)



Trouble shooting:
If you can't login under SQL Server Authentication check your logs files after a failed login attempt. They're located here by default:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

Look in the LOG file for anything like:
2006-08-16 09:31:16.33 Logon Error: 18456, Severity: 14, State: 8.

&quot;State: 8&quot; indicates that there's something wrong with a User Password

Here's a list of these different state messages in the SQL Server LOG
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

Brian > Thank you very much.

Before I found your description I've spend A LOT of time trying to figure it out. I wish there was more detailed people like you :-)

Regards
Kenneth

Brian thanks a lot i have been reading a lot and i was confused before reading
ur description;

thanks again ^_^

After exhausting all options, I abandoned v3.0.3 and downloaded 3.0.5 because I am running SQL Server 2005 Developer Edition instead of SQL Express.

Installed perfectly on the first try.

It appears v3.0.3 does not work with SQL Server 2005 Developer Edition.