SQL Authentication and SQL Express

SQL Authentication and SQL Express

• LenardG

  Hi,
  
  Please follow instructions below:
  
  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.
  
  Regards,
  Vikram

  
  
  

• wingsalltheway19

  Greetings

  I too am having SQL auth login problems with SQL Server Mgmt tool. I tried the above REG mod (seems like it took), then restarted machine. Now I get the below meesage when trying to use SQL auth. BTW, I'm on a stand-alone home machine. I try 'sa', no pwd and no go. I can easily login with Win auth, but am unable to attach to a DB in my mydocs/website folder as it doesn't see it. I used to have MSDE running but yanked it. I think it has something to do with my Win admin user account, though it has full machine access. I'm running XP Pro. As you can tell, I am somewhat new to this and would appreciate any haelp or direction. I really just want to to attach to a db in my docs folder. I could care less about which is the proper method. I just want one to work.

  Cannot connect to PDG1\SQLEXPRESS

  A connection was successfully established with the server, but then the an error occured during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)(Microsoft SQL Server, Error: 233)

  Regards

  Sully

  
  
  

• Ralph Deleja-Hotko

  I did the registry change and changed to mixed mode in the Mgmt Studio and now SQL Authentication works for me. Thanks for all the tips.
  

• beam

  Yes SQL Server Express does support SQL Server authentication but it does not by default. You need to change it.
  
  -Euan

  Please reply only to the newsgroup/forum so that others can benefit. When posting,please state the version of SQL Server being used and the error number/exact error message text received, if any.
  
  Use of included script samples are subject to the terms specified at
  http://www.microsoft.com/info/cpyright.htm

  This posting is provided "AS IS" with no warranties, and confers no rights.
  

  
  
  

• romeo123

  Euan,
  
  That's interesting.  "SQL Server Express DOES support SQL Server authentication but it does not by default.  You need to change it."
  
  I'll bite.. How
  
  
  

• MikeNZ

  I am having the same problem.  Its bad enough that I have to use T-SQL to add a new user and grant permissions and access to every specific database...then to find out that you get your error after trying to connect using sql authentication.  The previous post says nothing about how you can change the default behavior....any ideas anyone  
  
  I am trying to get dotnuke to work using sql authentication...and its been a nightmare using this CTP.  I had to do these commands in express manager to get a user added.
  
  use SUNSPRAY
  exec sp_addlogin 'oob', 'oob191EGrandAve', 'SUNSPRAY', 'us_english'
  EXEC sp_addsrvrolemember 'oob','sysadmin'
  EXEC sp_grantdbaccess 'oob'
  
  It then shows as a user for my SUNSPRAY database, but when i try to connect using SQL...it blows up...it also blows up when i try to connect using VS 2005 beta 2.
  
  
  
  

• Miles Cohen

  For me, the fix was to enable the SQLBrowser (also known as "SQL Server Browser") service.
  
  

• MSNasianuk

  Okay, I figured out my problem.

  I have no idea if this will help at all, but I have figured out a way to get SQL Express 2005 to SQL Authenticate via the Server Mgmt tool. Let me first say that I am a novice, so this message is for the person who has little or no clue. I am running Wxp Professional on a stand alone client machine (home box).

  First log in via Windows Authentication. If you can't log on via Windows Auth then read no further and check User Account priviledges and set accordingly. User Accounts can be found in the Control Panel > User Accounts. Check to see if you have Administrator Access on machine. If not, set it and continue below. If you do have Admin access and SQL Win Auth still fails uhh read no further and good luck.

  1. Since you can log on via Win Auth, you'll need to right click 'your server' and choose 'properties'. Go to 'Security' and select 'Mixed Mode'. Close SQL EMT.
  2. Go to your computer, Start > Control Panel > Admin Tools > Services panel. Scroll down to view your SQL services and then one-at-a-time right click each (you probably don't need to do all, but I went for it) and select properties.
  3. Click the 'Log On' tab and select 'Local System account' and 'Allow service to interact..' check box (nothing else), then click 'OK'.
  4. You should be back at the main Sevices panel. Using the cassette player icons at the top of panel, simply stop, then start each SQL service (there's a stop start button does both auto).
  5. After restarting each, close window and try logging in using SQL Auth. For the real beginners: username: sa pwd: leave blank.

  Bingo!

  hope this helped

  Sully

  
  
  
• SQL Authentication and SQL Express