ODBC Driver .... User cannot login ...

Although I am not working on the systems right now, while setting up replication, all of a sudden both systems began giving this error when I attempted to access the Web Portal login screen.
Any suggestions.yep...some sql servers when set up may have been set up using collations that are case sensitive, which means that usernames & passwords will be case sensitive also.

Also, make sure the ODBC is set up to use TCP sockets ( if 7,2000 ) or named pipes if 6.5 ...just seems to be better that way.

Also make sure that you have at least one database the login has access to & the login has access granted to it.

Post back if probs.

J.

ODBC Connection with SQL Express

If I create any other user (test, felix, fido, etc) in my database, that user can login successfully through Management Studio Express except not through an ODBC connection. Thus, this is a very bizarre issue. I have made sure this user has the same roles as SA. My instance is configured to handle SQL Server Authentication and Integrated. This works fine in Studio Express, but not in the ODBC connection.

When using "SA" login, I can connect to Management Studio Express and through an ODBC connection. Seems rudimentary enough, however, if I change the name in the ODBC connection i get

"Connection Failed,

SQLState '28000', SQL

Server Error 18456,

Microsoft SQL Native Client SQL Server Login failed for user 'xyz'"

Any ideas? Do I need a different ODBC Administrator?

Hi,

I would suggest to refer http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx for connection troubleshooting.

Hemantgiri S. Goswami

|||

Most likely this is caused by the fact that you are connecting (or trying to connect) to a database within your connection string where you don′t have access to. The state in the error code should give you a bit more information about the problem during the connection time. try setting a database in the connection string which is in common accessible to everyone (master). Although this might work, you should consider putting the right database in the connection string afterwards, as changing database it not a cheap operation.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

ODBC Connection Hangs after 15 minutes of non-use

I have several workstations that login to a WIN_2000 Server and Connect just
fine to a SQL-2000 Server (different machines) over a dedicated VPN....
They have a x.x.2.1 addressing scheme and we have x.x.1.1 addressing scheme.
Everything works beautifully for a while.
My application runs a MailMerge with Word and Access 9.0 with tables linked
to the SQL Server 2000 server.... The code uses a pre set up ODBC
connection (SQL Server) that connects just fine right after start up...
Code works and ODBC connection works.... However, after 15-45 minutes the
ODBC connection stops working... This can be verified by testing the ODBC
applet which hangs
I have 50 local users who have no issues with the setup - only these few
remote users.
I have set the server autoconfigure to never disconnect. (I can still
browse the servers)..
Any clues as to why the ODBC connection would become disabled after a short
period of time - requiring a reboot to work again?To me this sounds like an issue with the VPN not ODBC and SQL Server.
Mike O.
"Bob" <rrowe68@.hotmail.com> wrote in message
news:%23jMJEVX1DHA.536@.tk2msftngp13.phx.gbl...

quote:

---

> I have several workstations that login to a WIN_2000 Server and Connect

---

just

quote:

---

> fine to a SQL-2000 Server (different machines) over a dedicated VPN....
> They have a x.x.2.1 addressing scheme and we have x.x.1.1 addressing

---

scheme.

quote:

---

> Everything works beautifully for a while.
> My application runs a MailMerge with Word and Access 9.0 with tables

---

linked

quote:

---

> to the SQL Server 2000 server.... The code uses a pre set up ODBC
> connection (SQL Server) that connects just fine right after start up...
> Code works and ODBC connection works.... However, after 15-45 minutes

---

the

quote:

---

> ODBC connection stops working... This can be verified by testing the ODBC
> applet which hangs
> I have 50 local users who have no issues with the setup - only these few
> remote users.
> I have set the server autoconfigure to never disconnect. (I can still
> browse the servers)..
> Any clues as to why the ODBC connection would become disabled after a

---

short

quote:

---

> period of time - requiring a reboot to work again?
>
>

---

ODBC Connection Failure

Hi,
I'm trying to create a ODBC connection to SQL server 2000 using SQL server authentication. I have login id "lucky9797" and password "tool" but I'm keep getting the following error message:
Connection Failed:
SQLState:'28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login Failed for user "lucky9797"
Could someone please tell me why am I getting this error message? Thanks.
Message posted via http://www.sqlmonster.com
1) It might be that the collation sequence indicated at installation time is
case insensitive. Try typing "TOOL" instead of "tool"
"syed rahman via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:0e43caa46e9040489d8c88e56a57a606@.SQLMonster.c om...
> Hi,
> I'm trying to create a ODBC connection to SQL server 2000 using SQL server
authentication. I have login id "lucky9797" and password "tool" but I'm keep
getting the following error message:
> Connection Failed:
> SQLState:'28000'
> SQL Server Error: 18456
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login Failed for
user "lucky9797"
> Could someone please tell me why am I getting this error message? Thanks.
> --
> Message posted via http://www.sqlmonster.com

ODBC Connection Failure

Hi,
I'm trying to create a ODBC connection to SQL server 2000 using SQL server a
uthentication. I have login id "lucky9797" and password "tool" but I'm keep
getting the following error message:
Connection Failed:
SQLState:'28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login Failed for
user "lucky9797"
Could someone please tell me why am I getting this error message? Thanks.
Message posted via http://www.droptable.com1) It might be that the collation sequence indicated at installation time is
case insensitive. Try typing "TOOL" instead of "tool"
"syed rahman via droptable.com" <forum@.droptable.com> wrote in message
news:0e43caa46e9040489d8c88e56a57a606@.SQ
droptable.com...
> Hi,
> I'm trying to create a ODBC connection to SQL server 2000 using SQL server
authentication. I have login id "lucky9797" and password "tool" but I'm keep
getting the following error message:
> Connection Failed:
> SQLState:'28000'
> SQL Server Error: 18456
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login Failed for

user "lucky9797"
> Could someone please tell me why am I getting this error message? Thanks.
> --
> Message posted via http://www.droptable.comsql

ODBC Connection

Hi, All,
I got problem with ODBC connection, I have a product server and a stage
server, I made a sysdsn called SQLDSN, create a com+ called login and just a
very simply statement: list below:
private oconn as new adodb.connection
Public Function vPWD() As Boolean
Dim lngID As Long
Dim intTemp As Integer
Dim intPWD As Integer
Dim strSQL As String
Dim oRS As New ADODB.Recordset
Call startConn
oConn.spLogin strName & "," & strPWD, oRS
If oRS.BOF And oRS.EOF Then
vPWD = False
Else
vPWD = True
strOpCode = oRS.Fields("Opcode")
If IsNull(oRS.Fields("aop")) Then
bisOrg = False
Else
bisOrg = True
End If
End If
oRS.Close
Set oRS = Nothing
End Function
Private Sub startConn()
On Error GoTo err_handler
If Not bConnActive Then
oConn.Open strDSN
bConnActive = True
End If
Exit_function:
Exit Sub
err_handler:
Resume Exit_function
End Sub
and create a instance of login from asp page,
ODBC is tested working fine both stage and product
firewall is turned off on stage server.
connection timeout set to unlimited.
security context is fine.
from com debug, odbc connection is fine, both with stage and product
the problem is, when I change the odbc link server from product to stage run
the login.asp it does not work, and I got error message
ADODB.Connection (0x800A0E78)
Operation is not allowed when the object is closed.
anyone can help will be appreciated.
Thanks
Yifei
I found solution:
set com+ application security check at process level
Thanks
Yifei
"Yifei" <yjiang@.sdg.aust.com> wrote in message
news:eNmKDvJxFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi, All,
> I got problem with ODBC connection, I have a product server and a stage
> server, I made a sysdsn called SQLDSN, create a com+ called login and just
a
> very simply statement: list below:
> private oconn as new adodb.connection
> Public Function vPWD() As Boolean
> Dim lngID As Long
> Dim intTemp As Integer
> Dim intPWD As Integer
> Dim strSQL As String
> Dim oRS As New ADODB.Recordset
> Call startConn
> oConn.spLogin strName & "," & strPWD, oRS
> If oRS.BOF And oRS.EOF Then
> vPWD = False
> Else
> vPWD = True
> strOpCode = oRS.Fields("Opcode")
> If IsNull(oRS.Fields("aop")) Then
> bisOrg = False
> Else
> bisOrg = True
> End If
> End If
> oRS.Close
> Set oRS = Nothing
> End Function
> Private Sub startConn()
> On Error GoTo err_handler
> If Not bConnActive Then
> oConn.Open strDSN
> bConnActive = True
> End If
> Exit_function:
> Exit Sub
> err_handler:
> Resume Exit_function
> End Sub
>
> and create a instance of login from asp page,
> ODBC is tested working fine both stage and product
> firewall is turned off on stage server.
> connection timeout set to unlimited.
> security context is fine.
> from com debug, odbc connection is fine, both with stage and product
> the problem is, when I change the odbc link server from product to stage
run
> the login.asp it does not work, and I got error message
> ADODB.Connection (0x800A0E78)
> Operation is not allowed when the object is closed.
> anyone can help will be appreciated.
> Thanks
> Yifei
>

ODBC Connection

Hi, All,
I got problem with ODBC connection, I have a product server and a stage
server, I made a sysdsn called SQLDSN, create a com+ called login and just a
very simply statement: list below:
private oconn as new adodb.connection
Public Function vPWD() As Boolean
Dim lngID As Long
Dim intTemp As Integer
Dim intPWD As Integer
Dim strSQL As String
Dim oRS As New ADODB.Recordset
Call startConn
oConn.spLogin strName & "," & strPWD, oRS
If oRS.BOF And oRS.EOF Then
vPWD = False
Else
vPWD = True
strOpCode = oRS.Fields("Opcode")
If IsNull(oRS.Fields("aop")) Then
bisOrg = False
Else
bisOrg = True
End If
End If
oRS.Close
Set oRS = Nothing
End Function
Private Sub startConn()
On Error GoTo err_handler
If Not bConnActive Then
oConn.Open strDSN
bConnActive = True
End If
Exit_function:
Exit Sub
err_handler:
Resume Exit_function
End Sub
and create a instance of login from asp page,
ODBC is tested working fine both stage and product
firewall is turned off on stage server.
connection timeout set to unlimited.
security context is fine.
from com debug, odbc connection is fine, both with stage and product
the problem is, when I change the odbc link server from product to stage run
the login.asp it does not work, and I got error message
ADODB.Connection (0x800A0E78)
Operation is not allowed when the object is closed.
anyone can help will be appreciated.
Thanks
YifeiI found solution:
set com+ application security check at process level
Thanks
Yifei
"Yifei" <yjiang@.sdg.aust.com> wrote in message
news:eNmKDvJxFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi, All,
> I got problem with ODBC connection, I have a product server and a stage
> server, I made a sysdsn called SQLDSN, create a com+ called login and just
a
> very simply statement: list below:
> private oconn as new adodb.connection
> Public Function vPWD() As Boolean
> Dim lngID As Long
> Dim intTemp As Integer
> Dim intPWD As Integer
> Dim strSQL As String
> Dim oRS As New ADODB.Recordset
> Call startConn
> oConn.spLogin strName & "," & strPWD, oRS
> If oRS.BOF And oRS.EOF Then
> vPWD = False
> Else
> vPWD = True
> strOpCode = oRS.Fields("Opcode")
> If IsNull(oRS.Fields("aop")) Then
> bisOrg = False
> Else
> bisOrg = True
> End If
> End If
> oRS.Close
> Set oRS = Nothing
> End Function
> Private Sub startConn()
> On Error GoTo err_handler
> If Not bConnActive Then
> oConn.Open strDSN
> bConnActive = True
> End If
> Exit_function:
> Exit Sub
> err_handler:
> Resume Exit_function
> End Sub
>
> and create a instance of login from asp page,
> ODBC is tested working fine both stage and product
> firewall is turned off on stage server.
> connection timeout set to unlimited.
> security context is fine.
> from com debug, odbc connection is fine, both with stage and product
> the problem is, when I change the odbc link server from product to stage
run
> the login.asp it does not work, and I got error message
> ADODB.Connection (0x800A0E78)
> Operation is not allowed when the object is closed.
> anyone can help will be appreciated.
> Thanks
> Yifei
>sql

Obtaining Window login name from a trigger

Hi,
We are running SQL 2K with SQL Server and Windows authentication. Our system has been designed to login external user to SQL Server using one login account. I am trying to retrieve that actual Windows login user name from a trigger. Does anyone know ho
w I accomphish this task.
Try SUSER_SNAME function:
http://msdn.microsoft.com/library/de...u-sus_86xx.asp
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Herb" <Herb@.discussions.microsoft.com> wrote in message
news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our
system has been designed to login external user to SQL Server using one
login account. I am trying to retrieve that actual Windows login user name
from a trigger. Does anyone know how I accomphish this task.
|||If you are using something like MTS / COM+ then you're not going to be able to get that information unless the component explicitly passes in the user name.
"Herb" wrote:

> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our system has been designed to login external user to SQL Server using one login account. I am trying to retrieve that actual Windows login user name from a trigger. Does anyone know
how I accomphish this task.
|||Hi,
Thanks for your suggestion. When I issue SUSER_SNAME while using Window Authication, I do get the Window user ID. However, when I issue SUSER_SNAME while using SQL login, I get the SQL login(for ex. SQL login = RUSER, SUSER_SNAME retrieves RUSER).
"Narayana Vyas Kondreddi" wrote:

> Try SUSER_SNAME function:
> http://msdn.microsoft.com/library/de...u-sus_86xx.asp
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Herb" <Herb@.discussions.microsoft.com> wrote in message
> news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> system has been designed to login external user to SQL Server using one
> login account. I am trying to retrieve that actual Windows login user name
> from a trigger. Does anyone know how I accomphish this task.
>
>
|||You'll need to pass the "real" login name through to SQL. One technique for
this when an application uses a single service account is to use SET
CONTEXT_INFO to pass the information through with the user connection and
pull this out in your trigger (i. Note that you have to explicitly set this
in your app for each connection that you require this information for. In
some configurations this may not be practical/possible. See BOL for more on
the command.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Herb" <Herb@.discussions.microsoft.com> wrote in message
news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our
system has been designed to login external user to SQL Server using one
login account. I am trying to retrieve that actual Windows login user name
from a trigger. Does anyone know how I accomphish this task.
|||Thanks for the suggestion. I will give that a try.
"Jasper Smith" wrote:

> You'll need to pass the "real" login name through to SQL. One technique for
> this when an application uses a single service account is to use SET
> CONTEXT_INFO to pass the information through with the user connection and
> pull this out in your trigger (i. Note that you have to explicitly set this
> in your app for each connection that you require this information for. In
> some configurations this may not be practical/possible. See BOL for more on
> the command.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Herb" <Herb@.discussions.microsoft.com> wrote in message
> news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> system has been designed to login external user to SQL Server using one
> login account. I am trying to retrieve that actual Windows login user name
> from a trigger. Does anyone know how I accomphish this task.
>
>
|||Thanks for that information. We still have some apps that are using COM+
Herb
"Shawn Brock" wrote:
[vbcol=seagreen]
> If you are using something like MTS / COM+ then you're not going to be able to get that information unless the component explicitly passes in the user name.
> "Herb" wrote:
w how I accomphish this task.

Obtaining Window login name from a trigger

Hi,
We are running SQL 2K with SQL Server and Windows authentication. Our syste
m has been designed to login external user to SQL Server using one login acc
ount. I am trying to retrieve that actual Windows login user name from a tr
igger. Does anyone know ho
w I accomphish this task.Try SUSER_SNAME function:
http://msdn.microsoft.com/library/d.../>
us_86xx.asp
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Herb" <Herb@.discussions.microsoft.com> wrote in message
news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our
system has been designed to login external user to SQL Server using one
login account. I am trying to retrieve that actual Windows login user name
from a trigger. Does anyone know how I accomphish this task.|||If you are using something like MTS / COM+ then you're not going to be able
to get that information unless the component explicitly passes in the user n
ame.
"Herb" wrote:

> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our system has
been designed to login external user to SQL Server using one login account. I am tr
ying to retrieve that actual Windows login user name from a trigger. Does anyone kn
ow
how I accomphish this task.|||Hi,
Thanks for your suggestion. When I issue SUSER_SNAME while using Window Aut
hication, I do get the Window user ID. However, when I issue SUSER_SNAME wh
ile using SQL login, I get the SQL login(for ex. SQL login = RUSER, SUSER_S
NAME retrieves RUSER).
"Narayana Vyas Kondreddi" wrote:

> Try SUSER_SNAME function:
> http://msdn.microsoft.com/library/d...
-sus_86xx.asp
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Herb" <Herb@.discussions.microsoft.com> wrote in message
> news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> system has been designed to login external user to SQL Server using one
> login account. I am trying to retrieve that actual Windows login user nam
e
> from a trigger. Does anyone know how I accomphish this task.
>
>|||You'll need to pass the "real" login name through to SQL. One technique for
this when an application uses a single service account is to use SET
CONTEXT_INFO to pass the information through with the user connection and
pull this out in your trigger (i. Note that you have to explicitly set this
in your app for each connection that you require this information for. In
some configurations this may not be practical/possible. See BOL for more on
the command.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Herb" <Herb@.discussions.microsoft.com> wrote in message
news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our
system has been designed to login external user to SQL Server using one
login account. I am trying to retrieve that actual Windows login user name
from a trigger. Does anyone know how I accomphish this task.|||Thanks for the suggestion. I will give that a try.
"Jasper Smith" wrote:

> You'll need to pass the "real" login name through to SQL. One technique fo
r
> this when an application uses a single service account is to use SET
> CONTEXT_INFO to pass the information through with the user connection and
> pull this out in your trigger (i. Note that you have to explicitly set thi
s
> in your app for each connection that you require this information for. In
> some configurations this may not be practical/possible. See BOL for more o
n
> the command.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Herb" <Herb@.discussions.microsoft.com> wrote in message
> news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> system has been designed to login external user to SQL Server using one
> login account. I am trying to retrieve that actual Windows login user nam
e
> from a trigger. Does anyone know how I accomphish this task.
>
>|||Thanks for that information. We still have some apps that are using COM+
Herb
"Shawn Brock" wrote:
[vbcol=seagreen]
> If you are using something like MTS / COM+ then you're not going to be abl
e to get that information unless the component explicitly passes in the user
name.
> "Herb" wrote:
>
w how I accomphish this task.

Obtaining Login ID from page

Hello,

By using DBCC Page I am able to exemine data entered to row and other changes related to the rows. What I want to know is; is it possible to find out (and how) which Login ID commited these change directly from pages or where do I need to look to find it out?

Any guidance is appreciated....

Two parts to this answer:

1. We don't document the on-disk structures except by special licensing/NDA agreement with ISVs

2. That information is not on the page anyway.

obtain sql server login name..

i'm updating an Access 2000 db thats linked to SQL server 2000, and want to
create separate id/passwords for each user, and create an audit trail for
data modifications.
Any suggestions for an audit trail?
How can Access obtain the sql server login for the current user to store on
table?
Thanks!"select system_user" will return the current user.
To track data modification, you either need to have history tables with
triggers on the main tables, or have a trace running. You can research both
in Books Online. Generally, people will maintain a history table with who,
when, and from what system the modification occurred.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"nycdon" wrote:

> i'm updating an Access 2000 db thats linked to SQL server 2000, and want t
o
> create separate id/passwords for each user, and create an audit trail for
> data modifications.
> Any suggestions for an audit trail?
> How can Access obtain the sql server login for the current user to store o
n
> table?
> Thanks!
>