How to add a new .mdf with ONLY sql authentication in SQL Express?

How to add a new .mdf with ONLY sql authentication in SQL Express?

• carlengerer

  oh yeah. . . you can't do SQL authentication only.

  its either windows or mixed mode

  
  
  

• Wild Dog McDog

  Carl,

  You are doing this programmatically

  
  
  

• RichardSQLDev

  Thanks Rene,

  I learned something. I don't need the SSMSE becuase I have the full version installed too.

  However I still can't fix the problem. This is what I have done so far:

  I exported a .regservr and the xml looks just like you show. authetication 0. So I went back and looked in the registry again. I found a LoginMode subkey but in a different key:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.4\MSSQLServer

  It didn't quite make sense, because I am trying to change a NAMED instance. As expected it did not work. The exported .regsrvr still shows the same authentication mode 0.

  I am scared to start adding registry keys that do not even exist.

  The nightmare continues.
  
  

  
  

  
  

• Kbowman

  I meant to ask you about a link to the thread you mentioned.

  What do you mean programmatically

  • Create a c# app that could access and modify these registry keys in code in which case NO.
  • or am I creating a connection object programmatically that does not work. Again NO. I can't even create a .mdf db that uses sql authentication, let alone try to connect to it in code.
    

  I am using the embedded GUI of SSE inside VS 2k5.

  The machine has another named full version of sqlserver and the full SSMS(studio). Unfortunately, SSMS is not usable to create a single file .mdf db. (unless there is a way I'm not aware of)

  It can connect to the named express instance, it attach an existing one, created some other way(albeit it modifies the owner) but it cannot create one. So I have to go to the VS IDE and add a .mdf file.

  What a mess. And I am still at the sandbox level. No real code. I think MS laid a total brick with SSE.

  Thank god, there is the full version.

  The only reason I was playing with SSE is to see how I can just do a quick single file db inside a c# web app and easily zip the whole project source code to someone else. The portability is appealing. However, the .mdf is tied to the profile on my machine, as the system tables are copied to the Local Settings dir for the current OS user. So it is not really that portable.

  Well I have not tried it but I am pretty sure it would work: ship the .mdf file along with the rest of the source code uncompiled project. The recipient would have to just delete the whole SSE local setting folder in his own profile. re-launching SSE and trying to open the .mdf would create a new fresh copy of the system tables and off she goes.

  As far as doing any real work with SSE: forget about it.

  
  

  
  

• Fei-tian

   

  Not true.... I spent the weekend working on this and a friend really helped my understand. You can do excellent work with SSE.

   http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=223272&SiteID=1&PageID=0

  post an email adress and I will send you the solution.

  
  
  

• Krueger

  "Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection."
  
  This seems that you didn’t activate Mmixed Authentication, its an error you are getting if you only specified the Windows Authentication. Try setting the authentication mode for the express instance:

  <snip>
  Another way to change the security mode after installation is to stop
  SQL Server and set the appropriate registry key for your installation:
  

  Default instance:
  HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode
  

  Named instance:
  HKLM\Software\Microsoft\Microsoft SQL Server\Instance
  Name\MSSQLServer\LoginMode
  

  to 2 for mixed-mode or 1 for integrated. (Integrated is the default
  setup for the SQL Server 2000 Data Engine.)
  </snip>
  
  HTH, Jens Suessmeyer.

  
  

• DennyM

   

  You can interrogate the database engine with the CTP SQL Express Mamnagement Suite downloadable from Microsoft.

  Click select the database engine..... right click and select export

  You'll get a file like this:

  < xml version="1.0" encoding="utf-8" >
  <Export serverType="8c91a03d-f9b4-46c0-a305-b5dcc79ff907">
    <ServerType id="8c91a03d-f9b4-46c0-a305-b5dcc79ff907" name="Database Engine">
      <Server name="Shhhh\sqlexpress" description="Local instance - 'bliss\sqlexpress'">
        <ConnectionInformation>
          <ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907</ServerType>
          <ServerName>Shhhh\sqlexpress</ServerName>
          <AuthenticationType>0</AuthenticationType>
          <UserName />
          <Password />
          <AdvancedOptions />
        </ConnectionInformation>
      </Server>
    </ServerType>
  </Export>

  You'll see that ny authentication type is 0 which is windows authentification.

  I ust went through this and there is a way to attach this programattically.

  Use the Sql Management Classes in your code....

  Create an XP UserGroup and user using the SMO classes. Then create the same user group in and user in the database and Attach it. It never fails and yes... I know how frustrating this is. I lost a lost of sleep over this this weekend.

  Check out the thread in the first forum of this board for more information.

  Renee

   

   

   

   

  
  
  

• QBert

  http://msdn2.microsoft.com/microsoft.sqlserver.management.smo.server.attachdatabase.aspx

  http://msdn2.microsoft.com/ms160723.aspx

  
  
  

• RuiA

  Thanks Jens,

  1. I made sure during the install I selected mixed mode.

  2. This article essentially says the same. However, I cannot find in the registry any "LoginMode" subkey for either the default SQLEXPRESS, or MySSE named instance.

  Actually I did a complete search and there is no Key, Value or Data that contains "LoginMode" in the whole registry.

   

   

  
  
• How to add a new .mdf with ONLY sql authentication in SQL Express?