error 18456 when trying to connect to 2005 using odbc

Hi all

I am using Windows XP SP2 with a Sql server 2005 Express installation. I created a test db with a user called test, schema also test. I gave the permission connect, db_datareader and db_datawriter. I can now connect into this user using WinSQL. But if I want to connect using a application which is using ODBC, then I get the error:

[SQLSTATE=28000 - [Microsoft][SQL Native][SQL Server]Login failed for user 'test'.[Native Error=18456]]

Because I connect using ODBC, I think there must be the problem. But I have to connect with ODBC. There is no other possibility.

any ideas ?

Regards, Waff

I found this out:

The server collation is defined to: Latin1_General_CI_AS

If I create a new login id in lowercase, then ODBC will change it to uppercase and then the server refuse the access. But why. CI means case insensitive, so doesn't matter if upper or lower case. should.

How do you use a id in lowercase and connecto via ODBC ?

You need to enable TCP in order for ODBC clients to connect. Check out http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx for information about doing this.

Mike

|||

Guess has something to do with the collation. I used Latin1_General_AI_CI.

If I create my login id in lowercase and try then to connect to the db using ODBC, then ODBC will switch my id to uppercase and so I can't logon.

Anyway, somehow, it must be possible to modify the server settings, so that it doesn't matter how I logon. lowercase or uppercase. I tryed with CI and CS but still them same.

ODBC is enabled but think has to do with the collation.

What are you using if you use id's as upper/lower case as Test ?

error 18456 when trying to connect to 2005 using odbc

Hi all

I am using Windows XP SP2 with a Sql server 2005 Express installation. I created a test db with a user called test, schema also test. I gave the permission connect, db_datareader and db_datawriter. I can now connect into this user using WinSQL. But if I want to connect using a application which is using ODBC, then I get the error:

[SQLSTATE=28000 - [Microsoft][SQL Native][SQL Server]Login failed for user 'test'.[Native Error=18456]]

Because I connect using ODBC, I think there must be the problem. But I have to connect with ODBC. There is no other possibility.

any ideas ?

Regards, Waff

I found this out:

The server collation is defined to: Latin1_General_CI_AS

If I create a new login id in lowercase, then ODBC will change it to uppercase and then the server refuse the access. But why. CI means case insensitive, so doesn't matter if upper or lower case. should.

How do you use a id in lowercase and connecto via ODBC ?

You need to enable TCP in order for ODBC clients to connect. Check out http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx for information about doing this.

Mike

|||

Guess has something to do with the collation. I used Latin1_General_AI_CI.

If I create my login id in lowercase and try then to connect to the db using ODBC, then ODBC will switch my id to uppercase and so I can't logon.

Anyway, somehow, it must be possible to modify the server settings, so that it doesn't matter how I logon. lowercase or uppercase. I tryed with CI and CS but still them same.

ODBC is enabled but think has to do with the collation.

What are you using if you use id's as upper/lower case as Test ?

Error 18456 Login failed for user domain\user

We have an application that logs into our SQL Server 2000 using ODBC. All
service packs and critical updates are current on the server and clients.
SQL Server uses mixed authentification and runs under a domain account, the
users login with their domain\username, or at least they used to. Nobody
seems to know exactly when this stopped working, and of course, nobody said
anything until this week. Attempting to login now fails with error 18456
login failed for user domain\username. I've foolwed all the debugging info I
can find on Microsofts site. Running osql, I can log in using windows
authentification and SQL Server accounts with no problem. Trying to use
domain\username fails. I've been through everything from DNS to SQL Server
settings to client settings with no resolution. Anybody have an idea where
else to look?
Any hints would be greatly appreciated.
BruceHi Bruce,
Thank you for use the newsgroup and it is my pleasure to help you with you
issue.
From you information, you application, which used to work fine now got
error message
Could you check if the ODBC connection is made through the standard
security of SQL Server of trusted connection security? Is the SQL Server
authentication mode match the security option of ODBC connection? Could you
using the Query Analyzer to connect the SQL Server? That is, if you use the
standard security, such as use the account 'sa', when using 'sa' in Query
Analyzer, could it connect the SQL Server. When it is a Windows
authentication, when connect the SQL Server by Query Analyzer, you should
choose the Windows Authenticatin in the Query Analyzer.
Looking forward to your response. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Baisong:
Using Windows Authentification or Sql Server Authentification both work
fine.
If I'm logged into the domain I can run
osql -S SERVER -d Test -E
or
osql -S SERVER -d Test -U SA -P password
with no problem I can connect through Query analyzer either way.
if I try
osql -S SERVER -d Test -U domain\username -P password
it fails with the message Error 18456 Login failed for user domain\username.
The application we are using was working with the domain\username login, I
can't use Windows Integrated security with it, and I really do not want to
setup and manage 50+ SQL Server accounts. I am assuming that something has
changed in the authentification / delegation process and am working through
that now. As far as I can tell nothing has changed, but obviously something
has.
Any ideas would be appreciated.
Bruce
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:EKzqmHs7DHA.1992@.cpmsftngxa07.phx.gbl...
> Hi Bruce,
> Thank you for use the newsgroup and it is my pleasure to help you with you
> issue.
> From you information, you application, which used to work fine now got
> error message
> Could you check if the ODBC connection is made through the standard
> security of SQL Server of trusted connection security? Is the SQL Server
> authentication mode match the security option of ODBC connection? Could
you
> using the Query Analyzer to connect the SQL Server? That is, if you use
the
> standard security, such as use the account 'sa', when using 'sa' in Query
> Analyzer, could it connect the SQL Server. When it is a Windows
> authentication, when connect the SQL Server by Query Analyzer, you should
> choose the Windows Authenticatin in the Query Analyzer.
> Looking forward to your response. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Bruce,
Thank you for your update.
For the 'osql -S SERVER -d Test -U domain\username -P password' which
failed with the error message, could you check if it is a valid SQL Server
login? You could check it by unfolder the database, unfoulder the
'Security', then in the logins, is the above 'domain\username' in the
logins?
Looking forward to your response. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Yes, it is a valid login and the password is correct. The account also has
privledges (dbowner + our SQL_Admin role) to access the test database. It
works fine with Windows Integrated Security, so I don't think it is related
to SQL Server privledges, my guess is that something has changed in the
authentification authorization or delegation between the domain and the
server or the client.
The client computers are running MDAC 2.8 or 2.7RTM, and component checker
says they are current and all is well. Servers and clients are current on
patches, SQL Server is current. I've been throgh every article I can find on
MSDN that remotely deals with this error, and everything seems to check out.
I'm sure there is some little thing I'm overlooking, but I'm totally stumped
at this point.
Bruce
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:ZZofg867DHA.2508@.cpmsftngxa07.phx.gbl...
> Hi Bruce,
> Thank you for your update.
> For the 'osql -S SERVER -d Test -U domain\username -P password' which
> failed with the error message, could you check if it is a valid SQL Server
> login? You could check it by unfolder the database, unfoulder the
> 'Security', then in the logins, is the above 'domain\username' in the
> logins?
> Looking forward to your response. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Bruce,
The exact same thing happen here - ODBC connections that had worked
stopped working. We THINK it was related to installing patches but not
the full SP3a running on SQL 2000. We know installing SP3a fixed it.
An easy test to see if it's the same problem - in the ODBC config, set
the server name servername.subdomain.domain.?, check with the network
people for the full name if you need to.
If it works then maybe install/reinstall SP3a on the server will help.
SQL DBA in Richmond, VA
PS - I know 4 locales that suddenly experienced this same problem.
Maybe triggered by Windows critical update?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Error 18456 - ODBC connection failed for domain user account

I have a user that her domain user account is failing to access a sql server
database while another persons domain account with the same permission to th
e
database works just fine. I remote into the workstation and test the odbc
connection with my user account and it worked just fine. Any help.
I am getting this message below
Connection failed Sql state 28000 error 18456 microsoft odbc connection
login failed for user bhcs/connie'Aboki,
This should be a straight-forward error that means: bhcs/connie has no right
to login on the server.
Check to be sure: the login is the proper one (not one spelled almost the
same), that connie was not denied login, etc.
RLF
"Aboki" <Aboki@.discussions.microsoft.com> wrote in message
news:4C4AD587-76B4-498F-9D1A-99ED809F7294@.microsoft.com...
>I have a user that her domain user account is failing to access a sql
>server
> database while another persons domain account with the same permission to
> the
> database works just fine. I remote into the workstation and test the odbc
> connection with my user account and it worked just fine. Any help.
> I am getting this message below
> Connection failed Sql state 28000 error 18456 microsoft odbc connection
> login failed for user bhcs/connie'

Error 18456 - ODBC connection failed for domain user account

I have a user that her domain user account is failing to access a sql server
database while another persons domain account with the same permission to the
database works just fine. I remote into the workstation and test the odbc
connection with my user account and it worked just fine. Any help.
I am getting this message below
Connection failed Sql state 28000 error 18456 microsoft odbc connection
login failed for user bhcs/connie'
Aboki,
This should be a straight-forward error that means: bhcs/connie has no right
to login on the server.
Check to be sure: the login is the proper one (not one spelled almost the
same), that connie was not denied login, etc.
RLF
"Aboki" <Aboki@.discussions.microsoft.com> wrote in message
news:4C4AD587-76B4-498F-9D1A-99ED809F7294@.microsoft.com...
>I have a user that her domain user account is failing to access a sql
>server
> database while another persons domain account with the same permission to
> the
> database works just fine. I remote into the workstation and test the odbc
> connection with my user account and it worked just fine. Any help.
> I am getting this message below
> Connection failed Sql state 28000 error 18456 microsoft odbc connection
> login failed for user bhcs/connie'

Error 18456 - ODBC connection failed for domain user account

I have a user that her domain user account is failing to access a sql server
database while another persons domain account with the same permission to the
database works just fine. I remote into the workstation and test the odbc
connection with my user account and it worked just fine. Any help.
I am getting this message below
Connection failed Sql state 28000 error 18456 microsoft odbc connection
login failed for user bhcs/connie'Aboki,
This should be a straight-forward error that means: bhcs/connie has no right
to login on the server.
Check to be sure: the login is the proper one (not one spelled almost the
same), that connie was not denied login, etc.
RLF
"Aboki" <Aboki@.discussions.microsoft.com> wrote in message
news:4C4AD587-76B4-498F-9D1A-99ED809F7294@.microsoft.com...
>I have a user that her domain user account is failing to access a sql
>server
> database while another persons domain account with the same permission to
> the
> database works just fine. I remote into the workstation and test the odbc
> connection with my user account and it worked just fine. Any help.
> I am getting this message below
> Connection failed Sql state 28000 error 18456 microsoft odbc connection
> login failed for user bhcs/connie'sql

Error 18456 - domain admins only users that can authenticate

Server 2003
SQL 2000 SP3a
I have my server set up and my ODBC client configured. When I log in as
myself, as a domain admin, I can set my authentication to Windows (in my
ODBC), when I log on as a Domain Users, I cannot.
I get and error 18456. My server properties show SQL Server and Windows
authentication is allowed and there is no semi-colon in my password. What
security setting am I missing to allow my domain users to authenticate via
Windows authentication?
TIAHi,
Did you gave domain OS user access to SQL Server?
If not; open Enterprise manager and use Security option to add the Domain
user/group to SQL Server.
Thanks
Hari
SQL Server MVP
"dsb" <dsb@.discussions.microsoft.com> wrote in message
news:F7A3CA7A-1012-4673-8CDF-9B4B66583A3A@.microsoft.com...
> Server 2003
> SQL 2000 SP3a
> I have my server set up and my ODBC client configured. When I log in as
> myself, as a domain admin, I can set my authentication to Windows (in my
> ODBC), when I log on as a Domain Users, I cannot.
> I get and error 18456. My server properties show SQL Server and Windows
> authentication is allowed and there is no semi-colon in my password. What
> security setting am I missing to allow my domain users to authenticate via
> Windows authentication?
> TIA

error : 'QUOTED_IDENTIFIER, ARITHABORT'

I have an error like this :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
Can anyone explain to me ? what does it means ?
How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
ThanksI assume this is from a maint plan job?
You have computed columns or views with indexes on them, and you are trying to reorganize the data.
Unfortunately the main wiz doesn't execute the correct SET command for its connection, meaning that
it can't rebuild using DBCC DBREINDEX. You can create a TSQL job yourself from where you specify the
appropriate SET commands and then execute DBCC DBREINDEx (or DBCC INDEXDEFRAG).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Kresna Rudy K" <kresnark@.yahoo.com> wrote in message
news:1ad0401c387e3$55b5a740$a601280a@.phx.gbl...
> I have an error like this :
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
> because the following SET options have incorrect
> settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> Can anyone explain to me ? what does it means ?
> How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
> Thanks|||It's true , this error came from maintainence plan job,
but I still don't understand because I don't use view or
computed column with indexes, I use standart version of
sql 2000, this version doesnot support index view.
Please explain to me,
Thx Tibor.
>--Original Message--
>I assume this is from a maint plan job?
>You have computed columns or views with indexes on them,
and you are trying to reorganize the data.
>Unfortunately the main wiz doesn't execute the correct
SET command for its connection, meaning that
>it can't rebuild using DBCC DBREINDEX. You can create a
TSQL job yourself from where you specify the
>appropriate SET commands and then execute DBCC DBREINDEx
(or DBCC INDEXDEFRAG).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"Kresna Rudy K" <kresnark@.yahoo.com> wrote in message
>news:1ad0401c387e3$55b5a740$a601280a@.phx.gbl...
>> I have an error like this :
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
failed
>> because the following SET options have incorrect
>> settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
>> Can anyone explain to me ? what does it means ?
>> How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
>> Thanks
>
>.
>|||Nothing stop you from creating an index on a computed column or a view in standard edition. Try and
you will see:
USE pubs
GO
CREATE view v with schemabinding
as select au_id, au_lname from dbo.authors
GO
create unique clustered index ix on v(au_id)
You can try below to see if you have indexes in view. Run in each database:
select * from sysobjects where type = 'V'
and objectproperty(id, 'IsIndexed') = 1
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"kresna rudy k" <kresnark@.yahoo.com> wrote in message
news:1aeb201c387f2$0aed5ff0$a601280a@.phx.gbl...
> It's true , this error came from maintainence plan job,
> but I still don't understand because I don't use view or
> computed column with indexes, I use standart version of
> sql 2000, this version doesnot support index view.
> Please explain to me,
> Thx Tibor.
>
> >--Original Message--
> >I assume this is from a maint plan job?
> >
> >You have computed columns or views with indexes on them,
> and you are trying to reorganize the data.
> >Unfortunately the main wiz doesn't execute the correct
> SET command for its connection, meaning that
> >it can't rebuild using DBCC DBREINDEX. You can create a
> TSQL job yourself from where you specify the
> >appropriate SET commands and then execute DBCC DBREINDEx
> (or DBCC INDEXDEFRAG).
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Kresna Rudy K" <kresnark@.yahoo.com> wrote in message
> >news:1ad0401c387e3$55b5a740$a601280a@.phx.gbl...
> >> I have an error like this :
> >>
> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
> failed
> >> because the following SET options have incorrect
> >> settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> >>
> >> Can anyone explain to me ? what does it means ?
> >>
> >> How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
> >>
> >> Thanks
> >
> >
> >.
> >

Error (1552) ODBC, Insert Execute Error

Hi, I have a program called dbms/copy that I use to conver clarion database tables. The program first creates the table, if already exists I delete and recreate. This part works fine. When it tries to insert the records I get the following error message:

Error (1552)

ODBC, Insert Execute Error -> Buffer Contained 100 Records but only 0 Records were written

I created a System DSN ODBC link to the SQL Server. I tried using the "sa" user, as well as creating a user and checking off all available permissions. It seems to be a permissions problem to me, although it appears I have permission to do everything. It is definately able to connect since it has no problem creating/deleting/recreating the table in the SQL Server database.

I tried doing the same import with an ODBC connection to a Microsoft Access database and it works without any problems.

Any idea what's giving me this error?I turned on ODBC tracing and the following errors showed up in the SQL.log file:

dbmswin7 f58-b90 ENTER SQLSetStmtAttr
SQLHSTMT 00E51F98
SQLINTEGER 6 <SQL_ATTR_CURSOR_TYPE>
SQLPOINTER 0x00000001
SQLINTEGER 0

dbmswin7 f58-b90 EXIT SQLSetStmtAttr with return code -1 (SQL_ERROR)
SQLHSTMT 00E51F98
SQLINTEGER 6 <SQL_ATTR_CURSOR_TYPE>
SQLPOINTER 0x00000001
SQLINTEGER 0

DIAG [24000] [Microsoft][ODBC Driver Manager] Invalid cursor state (0)

dbmswin7 f58-b90 ENTER SQLSetStmtAttr
SQLHSTMT 00E51F98
SQLINTEGER 7 <SQL_ATTR_CONCURRENCY>
SQLPOINTER 0x00000003
SQLINTEGER 0

dbmswin7 f58-b90 EXIT SQLSetStmtAttr with return code -1 (SQL_ERROR)
SQLHSTMT 00E51F98
SQLINTEGER 7 <SQL_ATTR_CONCURRENCY>
SQLPOINTER 0x00000003
SQLINTEGER 0

DIAG [24000] [Microsoft][ODBC Driver Manager] Invalid cursor state (0)

dbmswin7 f58-b90 ENTER SQLSetStmtAttr
SQLHSTMT 00E51F98
SQLINTEGER 5 <SQL_ATTR_ROW_BIND_TYPE>
SQLPOINTER 0x00000000
SQLINTEGER 0

dbmswin7 f58-b90 EXIT SQLSetStmtAttr with return code 0 (SQL_SUCCESS)
SQLHSTMT 00E51F98
SQLINTEGER 5 <SQL_ATTR_ROW_BIND_TYPE>
SQLPOINTER 0x00000000
SQLINTEGER 0

dbmswin7 f58-b90 ENTER SQLSetStmtAttr
SQLHSTMT 00E51F98
SQLINTEGER 27 <SQL_ATTR_ROW_ARRAY_SIZE>
SQLPOINTER 0x00000064
SQLINTEGER 0

dbmswin7 f58-b90 EXIT SQLSetStmtAttr with return code 0 (SQL_SUCCESS)
SQLHSTMT 00E51F98
SQLINTEGER 27 <SQL_ATTR_ROW_ARRAY_SIZE>
SQLPOINTER 0x00000064 (BADMEM)
SQLINTEGER 0

dbmswin7 f58-b90 ENTER SQLBulkOperations
SQLHSTMT 00E51F98
SQLSMALLINT 4

dbmswin7 f58-b90 EXIT SQLBulkOperations with return code -1 (SQL_ERROR)
SQLHSTMT 00E51F98
SQLSMALLINT 4

DIAG [S1092] [Microsoft][ODBC SQL Server Driver]Invalid attribute/option identifier (0)

dbmswin7 f58-b90 ENTER SQLRowCount
HSTMT 00E51F98
SQLLEN * 0x0012F7F4

dbmswin7 f58-b90 EXIT SQLRowCount with return code 0 (SQL_SUCCESS)
HSTMT 00E51F98
SQLLEN * 0x0012F7F4 (0)

dbmswin7 f58-b90 ENTER SQLDisconnect
HDBC 00E515E8

dbmswin7 f58-b90 EXIT SQLDisconnect with return code 0 (SQL_SUCCESS)
HDBC 00E515E8

Originally posted by UltraSmooth
Hi, I have a program called dbms/copy that I use to conver clarion database tables. The program first creates the table, if already exists I delete and recreate. This part works fine. When it tries to insert the records I get the following error message:

Error (1552)

ODBC, Insert Execute Error -> Buffer Contained 100 Records but only 0 Records were written

I created a System DSN ODBC link to the SQL Server. I tried using the "sa" user, as well as creating a user and checking off all available permissions. It seems to be a permissions problem to me, although it appears I have permission to do everything. It is definately able to connect since it has no problem creating/deleting/recreating the table in the SQL Server database.

I tried doing the same import with an ODBC connection to a Microsoft Access database and it works without any problems.

Any idea what's giving me this error?

error - ODBC sql server driver timeout expired

Hi everyone

I have installed SQL server on my server machine. using localsystem and mixmode option.
It is working fine.

I installed sql server with connectivity tools only option on my client computer.

now, When i try to connect to server using data source from control panel --> Administrative tools --> Data source(ODBC).

but I cant connect.It gives me error as below.

Connection Failed:
SQLState:'HYT00'
Sql Server Error:0
[Microsoft][ODBC SQL Server Driver] Timeout Expired.

I uninstalled sql server from my client and server computer. and installed again.

I tried to ping from client computer to server computer it is working.

even i tried telnet sqlservername 1433

this one is also ok.

But same error facing

Please help me out.

Regards,
ASIFWhat's the client OS? What service pack? What version of MDAC is the client running? What's the status of NetBIOS on your network?

What OS is the server running? Waht service pack for the OS? What version of SQL? What service pack?

Any firewalls running between your client and the server?

You'll need to research these to get to the root of the problem...

Regards,

hmscott

Error

receiving error during backup.

Microsoft SQL-DMO (ODBC SQLState: 42000)

The media set for database '%' has 2 family members but only 3 are provided. All members must be provided. BACKUP DATABASE is terminating abnormally.

You might want to check your backup device definition. Remember that you can't mix a multi-file device with a single file device.