YesRe: Unable to authenticate on sql server 2005

Other than have incorrect credentials (such as a bad password), is the database configured for mixed mode security?

<ConnectionString>data source=(Local);packet size=4096;integrated security=SSPI;persist security info=False;initial catalog=PartsTrakUS</ConnectionString>

You should be admin on your own server, (if you installed with your current login).

connection.Connectionstring = " above string.

connection.Open();

We have had some problems with this when authenticating to a domain, and then unplugging the network.

We have told MS about the problem with no real resolution. Use sql authentication is a good idea, however you can also go into surface area configuration and configure for local only.

Thanks for the quick reply from everyone.

The database is however setup for mixed mode and I don't really see problems with the connection string other than the password you mentioned. Iv been working on this problem for a while now so reseting wont solve something here and I can't just use this for local as it will as soon as testing can be done upload the site its using.

I have thought the possibility that maybe my permissions are incorrect for the users on the database. Can anyone maybe direct me on the correct measures on creating one using the management studio?

Shouldn't the Initial Catalog (or Database) parameter value be your database name and the AttachDbFilename value be the path to your MDF?

Server=.\SQLExpress;AttachDbFilename=c:\..\..\mydbfile.mdf;Database=dbname;User ID=MyUsername;Password=MyPassword;Integrated Security=False;User Instance=False

I think there is an error in connection string

Data source=.//SQLEXPRESS

this is wrong

Data source=./SQLEXPRESS

Initial Catalog there is my database name, the AttachDbFilename is usually the path.
Im about to install SQL Server Dev edition to see if this may correct my issue, the "Data source = .//SQLEXPRESS" is correct here as // is seen as a single due to the fact that it sees as expecting a escape char to follow.

Anyway working on that install, will update in a little to let note of the progress. Thanks everyone for the replies, but i would like to make note once again that I dont think the connection string is at fault i still feel its the creating of a user or an error there of some kind. This due to the fact that it works with windows authentication. Anyway, if you got any more ideas ill love em.

Thanks

Hi,

To be sure if there is nothing wrong in your connection string do the following:

Create a connection to your database using Visual Studio Wizards (by adding a SQLConnection object in a form for example).

Test the conenction u created and if the test passes copy the connection string generated by Visual Studio.

HTH.

Hayder Marzouk

Hi again,

Well i checked out the wizard just to make double sure there wasn't a flaw there. This was now after installing SQL Server 2005 Dev Edition. Anyway I could now for the 1st time actually connect using a username and password so this is a milestone I have reached. How ever now since dev edition was installed im still unable to connect, heres the error I get:

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)

Also here is the new connection string im using to just as a test, this does work when testing using the facilities offered by the wizard and does return success:

string strConnection = "Data Source=RYAN; Initial Catalog = ASPNETDB.MDF; User ID=test; Password = test";

Any ideas here?

Thanks so far

The problem here is Initial Catalog refers to the database name and not the file name of the database.

So try:

string strConnection = "Data Source=RYAN; Initial Catalog = ASPNETDB; User ID=test; Password = test";

Or if you are not sure of the actual database name, then try connecting to the master database as integrated user and then run select name from sys.databases, like so:

' START SCRIPT
' This is a simple vbscript to test connecting and running a sql statement
' Save this as c:\test.vbs and then run cscript c:\test.vbs
Dim conn
set conn = createobject("adodb.connection")
conn.open "Provider=SQLOLEDB;Data Source=.;Integrated Security=SSPI;"
set rs = conn.execute("select name from sys.databases")
wscript.echo "Here is list of database names ->"
while not rs.eof
wscript.echo rs.fields(0).value
rs.movenext
wend
' END SCRIPT

Hi,

I ran the script you mentioned above just to be double sure about the name. It was ASPNETDB.MDF, although with a file extention i just created like that originally, probably not the best way to go.

I read another forum that this error could be caused by 1 of 2 reasons. 1 being remote access on the current machine, how ever once again I have actually activated that so it doesnt seem to be a problem and the 2nd being about the machine.config trying to load sqlexpress when infact im running server 2005 dev edition.

Anyway, still clueless at the moment, but trying anyway

Ryan
Hi,

Finally I figured out, the problem was that SQL by default within the machine.config has a string defined which seems to want to use Express. The problem was corrected using the following code:

<connectionStrings>

<remove name="LocalSqlServer"/>

<add name="strConnection" connectionString="Data Source=RYAN;Initial Catalog=ASPNETDB.MDF; User ID=test; Password=test" providerName="System.Data.SqlClient"/>

<add name="LocalSqlServer" connectionString="Data Source=RYAN;Initial Catalog=ASPNETDB.MDF; User ID=test; Password=test" providerName="System.Data.SqlClient"/>

</connectionStrings>

<appSettings>
<add key="strConnection" value="Data Source=RYAN; Initial Catalog = ASPNETDB.MDF; User ID=test; Password = test providerName = System.Data.SqlClient"/>
</appSettings>

Anyway, it works now although alot of that code seems redundant.
Yes I find that these .NET .config files are a horrible mess to deal with. You should not need to modify machine.config, this is my understanding. But if something is modified in the machine.config, it will affect everything.