I installed SQL Server Express 2005. I choose Mixed MOde Authentication and
then installed it.
I am trying to migrate data from an ODBC database source. I have windows xp
professional.
I was having trouble when I did "test connection" but I finally got it
successful connected.
I connected from SQL Server Studio mgmt to the server and established an SA
password.
I connected from the ODBC source using the password and sa login.

However, my laptop lost connection to the internet. I had to reboot and SQL
server defaulted
to Windows authentication mode. I wasn't too happy but somehow I got the
ODBC connection
to be reestablished and use Windows authentication in the control panel. So
everything should be great. THe ODBC connection tested positive.

However when I connect from the ODBC database and run a script I get errors
on entering the login and password. My SErver is called :
JANIS\SQLExPRESS (SQL Server 9.0.1399 - JANIS\janis)

So I try the login JANIS\SQLEXPRESS with my network password? That doesn't
work.
JANIS is the ODBC connection name. JROUGH is the server name. JANIS is my
computer name.
NOw I can't run a script from the ODBC data source even though the ODBC
connection tests positive and I can login to the server via SQL SErver mgmt
studio with the sa password.

IT asks for the password and login . The sa password doesn't work nor does
the computer login and password work. HOw can I fix this? Is there
something in Mgmt studio I need to change an object in the permissions area?
I am not sure if I gave it the ability to create instances since I used mixed
mode and now it is changed to use windows authentication.
thanks very much.

Janis (Janis (AT) discussions (DOT) microsoft.com) writes:
Probably because you had changed authentication to Windows only. The
setting does not take effect until SQL Server is restarted.

9.0.1399 is the RTM version of SQL 2005. While it may not resolve your
issues, there is all reason to download and install Service Pack 3.

Where do you try this? When you log in with Windows authentication, you
log in without specifying username or password; you always log in with
the same user as you have logged into Windows.

This is where it gets difficult. You have some piece of software, here
only identified as "ODBC data source", which try to log in to SQL Server
but which apparently fails. From what you said, I would assume that you
have everything on the same machine, but I am not sure.

If you are able to log in as sa through Mgmt Studio, there should be
no differences. Here are a few things to examine:

o Make sure that you are logging into the correct instance, and not
some other instance.
o Make sure that you enter the password exactly, and keep in mind that
it's case-sensitive.
o If there are non-ASCII characters, this may cause problems if the
applicaition is a command-line program.
o As I said above, you cannot specify a Windows login, when you log
in with Windows authentication.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

It was a problem with case in the password. User error. Thanks.
However, I cannot log in with the sa password from Mgmt Studio. I can only
log-in with windows authentication now. I definitely could before as I had
someone configuring for me.
I cannot login now. I made several attempts.
This is the error message "Login failed for user 'sa'. (microsoft SQL
Server, Error, 18456)
Why is that? I could before? All that happened is the Server rebooted and
now it defaults to Windows authentication and it works with that so I will
let it alone but I may need to be able to login as the 'sa' at from another
computer and I wonder how to release that?

"Erland Sommarskog" wrote:

I got the exact spelling of the windows login with the case and I rebooted
several times
and got the exact login. I still cannot login from the datasource. The
file is a Filemaker 10 file I need to migrate the data from Filemaker to SQL
Server. The odbc source is set up in windows and that connects on the test.
The SQL Server Mgmt studio connects also but only as Windows authentication.
There is no other instance that I can see that works so that probably isn't
it. I cannot connect to Mgmt studio to sQl server with the sa password. I
moved the dialog to authtenticate with sa password inserted the password that
definitely worked before and it no longer works. I really don't want to
reinstall the server.
thanks for your help.

"Erland Sommarskog" wrote:

I think the reason this Filemaker database cannot perform a script to Write
data to the SQL Server is because it needs the 'sa' login/password to write
to the database. Since it is not Windows it doesn't know anything about the
windows authentication. Since somehow the Windows authentication made my
'sa' password not work anymore now Filemaker cannot login either even though
the ODBC works and the Mgmt STudio works. The sa password doesn't seems to
work anymore. In fact, when I look at the objects in the security there is
the data engine object which has windows authentication. it doesn't allow
sql server authentication in that object
Then there is sa login which only lets you login with that password possibly
when it is in mixed mode but since it isn't in mixed mode it is rejecting the
sa account. The mgmt studio doesn't let me use the 'sa' account anymore.
When I use the 'sa' login in Filemaker odbc it doesn't work neither does the
windows authentication work on that side.
I think I need to turn this back into mixed mode authentication. HOw can I
do that.
thanks for all your help.

"Erland Sommarskog" wrote:

I checked my login on windows. I can login with my computer name and my
password or my windows login and my password. Either works. When I change
the sql configuration to "this account" and specify login such as
mycomputername\mylogin + password
I get the error message "the specified network password is not correct.
When I login with just mylogin + password
I get the same error message.
But I can log into Windows as I said with either the computer name or the
login.
So I don't know how to change this back to mixed mode.
I think if I do then everything will work.
thanks,

"Erland Sommarskog" wrote:

I tried to change the sql server config to login
"this account" instead of built-in account. Trying to get it back to mixed
mode.
When I do that as my windows login and password it says "a member could not
be added to or
from the local group because that member does not exist.Sometimes it says my
password is not correct. When I specify the computer name\login + password or
the login + password.
As I said I get the password is incorrect. I am using the right password
with the right case.

I did install service pak 3.

Also when I try the built in accounts with either local system, local
service or network account.
It as I said it says the network password is incorrect. What is "this
account" referring to?


"Erland Sommarskog" wrote:

Erland:
I got it back in mixed mode. Installing the service pack 3 reset it. It
was almost
like reinstalling sql server express but it let me put in default, and put
in a sa account
over again. Filemaker ODBC couldn't work with Windows authentication so it
had to be
in mixed mode. The ODBC DNS tested and SQL SErver mgmt studio lets me login
in as the
sa and it all works. Thanks for your help. Really needed to get this
project done so I
do appreciate that someone answers these emails .

"Erland Sommarskog" wrote:

Janis (Janis (AT) discussions (DOT) microsoft.com) writes:
Well, I do have a good excuse for not answering all your posts - you
made them from 3:16 to 6:20 in my time zone. I tend to be asleep at
that time. :-)

(And, yes, I had all Monday to reply, but during daytime I'm billing
time at client, and last night I was busy with private things.)

Anyway, it seems that things sorted out.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx