Users in SQL Express server
This is my connection routine for an SQL Express database I'm using with ASP.NET and C#
connectionString = "server=.\\SQLExpress;uid=sa;pwd=;database=cartridge;" +
"Integrated Security=True;";
connection = new SqlConnection(connectionString);
connection.Open();
When I run the database in "debug" mode through the VWD IDE the connection is made and I get query data etc, but when I try to access the database from IIS I get a login failed message, I assume this means I must create another SQL user? I can't find any option to do this in the GUI.
Please advise on this issue, it will be much apprecaited.
Alex Ellis
Server Error in '/print' Application.
Cannot open database "cartridge" requested by the login. The login failed.
Login failed for user 'D3MF2Q1J\ASPNET'.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details:System.Data.SqlClient.SqlException: Cannot open database "cartridge" requested by the login. The login failed.
Login failed for user 'D3MF2Q1J\ASPNET'.
Source Error:
Line 28: connection = new SqlConnection(connectionString);
Line 29: connection.Open();
Line 30: }
Line 31: |
Source File:c:\projects\CartridgeTracker\App_Code\CartAPI.cs Line:29
Hi,
Your connectionstring is wrong. SQL Express supports Windows Authentication as well as SQL Server authentication. You have mixed up both in the connectionstring.
If you are specifying the username and password then you should not set the Integrated Security to true. Remove the Integrated Security part from the connection string and it should work.
Regards,
Vikram
I did as you prescribed, but unfortunately things aren't any better, see the error below.
I would appreciate it if you could tell me what the best practice is for the following since as you said I
am confused and can't find any good documentation about this issue.
How can I create an SQL user in SQL Express 2005 and use it with my ASP.NET application?
How can I create a user account and authenticate to SQL Express 2005 with my ASP.NET application?
Regards,
Alex Ellis
Server Error in '/print' Application.
Login failed for user 'D3MF2Q1J\Administrator'. The user is not associated with a trusted SQL Server connection.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'D3MF2Q1J\Administrator'. The user is not associated with a trusted SQL Server connection.
Source Error:
Line 28: connection = new SqlConnection(connectionString);
Line 29: connection.Open();
Line 30: }
Line 31: |
Hi,
The problem is you are using a Windows user for a SQL Authentication. Anyways I will give a step by step procedure this time.
Enabling SQL Authentication
STEP1:
You need to enable SQL Authentication on the machine. This is complex in SQLExpress and you need to edit the registry. Copy the following lines of text onto a Notepad and save it with extention .REG.
-COPY BELOW
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer]
"LoginMode"=dword:00000002
-COPY TILL THIS
STEP2: Double Click on the .REG file to change the registry. This change the Authentication Mode to enable SQL Authentication.
STEP3: Restart the SQL Server Express Service or reboot the machine for registry change to take effect.
Next, you need to add a user with SQLLogin. To do this: Login to SQLExpress using Express Manager and run the script below:
-
USE MASTER
sp_addLogin 'aspuser', 'aspuser@123'
GO
sp_addsrvrolemember 'aspuser', 'sysadmin'
GO
USE Cartridge
EXEC sp_adduser 'aspuser'
-
In case you want to reduce the privilege for the user then you can specify something other than sysadmin, but we can think of that once this works.
Now change your connection string as follows:
connectionString = "server=.\\SQLExpress;uid=aspuser;pwd=aspuser@123;database=cartridge;";
Regards,
Vikram
Many thanks Vikram, it was just what I was looking for.
Regards,
Alex
Thank-you Vikram I was having this problem with IIS and your solution solved my problem and made me very happy too !!!
I am currently having problems with creating a new userthis happens under visual web developer 2005
here is what it looks like:
I would like to also say that i do enter a minimum of 7 char
and also I enter one numeric char. This occurs under the Timetracker kit,
what happens is that when the program is executed it will go into
login page if you dont have a user and password name then
you'll get the option of creating a new user
Every time I enter a password the same error comes up.
It is actually asking for a password like this:
a6B29r1!
Notice the exlamation mark. Any character such as this, in addition to the password combination you are already using will work just fine.
Rule of thumb: at least 7 characters consisting of 1 number, 1 letter and then 1 character that is not a number or letter ( like !,?,#,%,^,&). Any of the characters appearing above your keyboard number line will do.
Happy Databasing 
** By the way: You can also create a new user profile, including any other web app configurations by using the following instructions:
In the Visual Basic IDE window, click 'Website' > 'ASP.NET Configuration'. This will open a new IE browser with all the configuration settings for your application.
If you want the grand tour and superb FOR FREE video instruction for any of the Visual Studio 2005 Express Editions, go to the Start Page and click on, "Video Series - Getting Started with Visual Web Developer 2005", or whatever Visual Studio Express edition you are using.
Thank You very much mntlnrg
everyhing is working find now and the videos are perfect
I have being working with them all night
this has being the most help tip I've got thank you very much
once again.
Hello,
I don't find this SQLLogin or Exress Manager. (Is this not included by the SQLexpress installed bij VS2005?). If so, where can i download a working version?
Thanks
Vikram,
Tried the reg punch, but it still isn't working. It seems that the instance of SQLEXPRESS that was created has a problem that uninstalling/re-installing SQL Server Express doesn't clear up. I have tried a re-install and told it to allow amix mode login, and I can't even log-in as sa. I keep getting the error below (and the SHARED MEMORY connection is enabled)
TITLE: Connect to Server
Cannot connect to PAVILION-A530N\SQLExpress.
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
BUTTONS:
OK
Hi
Vikram, ive been reading your post and it seems very helpful for enabling a connection to SQL server express. Can you or anyone else tell me where i can find the SQLLogin to add a new user? I really cant seem to find it within the SQL Server Configuration Manager?
Thanks
User instances are not supported in common language runtime (CLR) code that is running inside of SQL Server. An InvalidOperationException is thrown if Open is called on a SqlConnection that has User Instance=true in the connection string.
The network protocol for user instances must be local Named Pipes. A user instance cannot be started on a remote instance of SQL Server, and SQL Server logins are not allowed.
So basically to deploy your application to the real world I recomend this: First attach the .mdf file to the SQL Express back it up then restore as a new DataBase then change your sql connection string in the web.config file to disable user instance then give the ASPNET account login permission on the server and the specific database.
Now for those of you who got the Login Controls to work but doesn't work remotely when viewed from the Internet; simply IExplore 6.0 doesn't handle the authentication ticket very well when your DNS its being "URLRedirected" or using "Frames" so just change the settings for your domain to do just the plain old "IP Address redirect to" method.
I hope this helps someone.
I am just a newbie trying to learn!
Darkonekt.
