Microsoft Technologies » .Net Framework » ASP.Net
I installed Microsoft SQL Server 2005 Express Edition but cannot get it connected from ASP.Net. What's the problem?
As discussed in the FAQ here, ASPNet user is not an administrator and SQL Server Authentication is not enabled, you need to do the following:
- Configure the server to work in mixed mode authentication
- Create a user with appropriate permissions
For SQL Server 2005 Express Edition, there is no GUI tool available to make it work in mixed mode authentication. You need to do it manually.
Open registry editor (launch application %WINDIR%\regedit.exe) and go to HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer in the tree on the left.
On the right, look for an entry named LoginMode. The default value, when installed 1. Update it to 2.
Launch your Service Manager (Start -> Run -> Type services.msc) and look for a service named MSSQL Server (SQLEXPRESS). Restart the service.
You are done with step 1. Hurray!
Now, to create a new user, login to SQL Server using the command-line utility - osql. Login using integrated authentication (ensure that you are logged in as a user with administrative priviledges), using the following command:
osql -S .\SQLExpress -E
One the SQL-command prompt, execute the following (do not type 1> or 2>, they will appear):
1> exec sp_addlogin 'username', 'password'
2> go
1> exec sp_addsrvroleadmin 'username', 'sysadmin'
2> go
1> quit
Replace the username and password with your entries. Do not forget to include the quotes.
Now, attempt login using the following on the command prompt:
osql -S .\SQLExpress -U username
Type the password, you should be able to login now! Great!
Last update: 2007-03-31 12:52
Author: Edujini
Revision: 1.3
Print this record
Send to a friend
Show this as PDF file
Export as XML-File
You cannot comment on this entry