Hi all.
I am going around in circles all day on this.
I have a clean new install of win XP SP2, VS 2005 Pro RTM,
and a NAMED instance of Sql Express with mixed authentication mode specified at setup.
The sa pw is 123456
I would have liked just SQL authentication but no such option.
I am trying to add a new mdf file. If I just use the "Add New Item" in VS web app project it just adds a new .mdf file with windows authentication tied to the current profile I am logged in the os with. I am trying to avoid that as this projects is to be zipped and downloaded as a sample app. I am also trying to avoid embedding an instance of SSE as the recipients of the zip file already have SSE running iwth thei VS IDE.
So most logical is to create the file with SQL authentication.
I have to do it by the Add Connection GUI
and for data source: "Microsoft SQL Server Database File (SqlClient) "
for data file name: C:\WebSites\Website1\App_Data\MyDatabase1.mdf (not yet existing file)
Use SQl server authentication:
user: sa, password: 123456 (the ones specified at set up of the SSE)
I keep getting
Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection.
I keep getting the same error no matter what username I use or password, the current login, etc. It just does not let me use sql authentication.
Please help anyone. This is a nightmare.
"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.
|||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.
|||
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
|||
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.
Carl,
You are doing this programmatically?
|||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.|||
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.
|||http://msdn2.microsoft.com/microsoft.sqlserver.management.smo.server.attachdatabase.aspx
http://msdn2.microsoft.com/ms160723.aspx
|||oh yeah. . . you can't do SQL authentication only.
its either windows or mixed mode
