Objects related to DB: user

Showing posts with label user. Show all posts

Friday, March 30, 2012

Odbc error

Dear all
My one user has installed sql server 2000 enterprise edition , SP3
(8.00.0860) on my computer.
I have created one odbc connection to sql server.
When he want to connect to sql user via odbc in application he gets
following error
Statement(s) could not be prepared
Unable to close cursor
[Microsoft][ODBC SQL Server Driver]Invalid cursor stat
What can be the problem?
RegardsWhat language is the application written in? At least the failing module?
I have seen similar errors in both T/SQL (implying a Stored Procedure) and C
(api level calls) modules.
I suspect it needs to be relinked with your new modules. To me, it sounds
like the api calls either are for a different ODBC release or have messed up
pointers.
If you can supply error numbers and complete error messages, it woulld help
--
Joseph R.P. Maloney, CSP,CCP,CDP
"amish" wrote:
> Dear all
> My one user has installed sql server 2000 enterprise edition , SP3
> (8.00.0860) on my computer.
> I have created one odbc connection to sql server.
> When he want to connect to sql user via odbc in application he gets
> following error
> Statement(s) could not be prepared
> Unable to close cursor
> [Microsoft][ODBC SQL Server Driver]Invalid cursor stat
> What can be the problem?
> Regards
>

ODBC DSN for SQL Database

Hi,

I am trying to configure DSN for a remote SQL database using Windows authentication. When I logn with a user ID and configure the DSN, it's working fine. But when I logout and login with another user ID, DSN test is failing with the following message.

Connection Failed:
SQL state 28000
SQL Server Error 18456
Microsoft ODBC SQLServer Driver SQL Server Login failed
for user.

Can any one please let me know how can I configure DSN so that it should work irrespective of what domain account I use to logon to the server later?

Thanks in advance.

Srini.Hi Srini

It sounds like only the first windows account (User ID) has access to SQLServer?

You can also test connectivity by trying to connect using one of the command line tools. I like to try this with SQLCmd (for SQL Server 2005) or osql (for SQL Server 2000)

Try either

sqlcmd -S<your server name here> -E
or
osql -S<your server name here> -E

The -E option tells the tools to use Windows Authentication.

Regards

Steve|||

Hello Steve my name is Chris and i have the same problem with the SQL that Srini has had but when i tried to run the "osql -S <My Server> -E i get the error Cannot generate SSPI context message please help me and point me in the right direction.

Thanks

Chris....

ODBC DSN for SQL Database

Hi,

Connection Failed:
SQL state 28000
SQL Server Error 18456
Microsoft ODBC SQLServer Driver SQL Server Login failed
for user.

Can any one please let me know how can I configure DSN so that it should work irrespective of what domain account I use to logon to the server later?

Thanks in advance.

Srini.Hi Srini

It sounds like only the first windows account (User ID) has access to SQLServer?

You can also test connectivity by trying to connect using one of the command line tools. I like to try this with SQLCmd (for SQL Server 2005) or osql (for SQL Server 2000)

Try either

sqlcmd -S<your server name here> -E
or
osql -S<your server name here> -E

The -E option tells the tools to use Windows Authentication.

Regards

Steve|||

Thanks

Chris....

ODBC DSN for SQL Database

Hi,

Connection Failed:
SQL state 28000
SQL Server Error 18456
Microsoft ODBC SQLServer Driver SQL Server Login failed
for user.

Can any one please let me know how can I configure DSN so that it should work irrespective of what domain account I use to logon to the server later?

Thanks in advance.

Srini.Hi Srini

It sounds like only the first windows account (User ID) has access to SQLServer?

You can also test connectivity by trying to connect using one of the command line tools. I like to try this with SQLCmd (for SQL Server 2005) or osql (for SQL Server 2000)

Try either

sqlcmd -S<your server name here> -E
or
osql -S<your server name here> -E

The -E option tells the tools to use Windows Authentication.

Regards

Steve|||

Thanks

Chris....

Wednesday, March 28, 2012

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.

Monday, March 26, 2012

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

Friday, March 23, 2012

ODBC Connection Problem

Hello,
I have an install of SQL that my users cannot connect to with an ODBC
connection. I have created Windows User logins on the SQL server through
Enterprise Manager, and have assigned them access to one specific database.
The specific user accounts have public, db_datareader and db_datawriter
permissions to the database. When the users attemtpt to create their ODBC
connections they receive an access denied message. What other permissions do
I need to give these users? Do they need any other permissions to the actual
server the SQL instance resides on?
Thanks,
JBaileyJBailey,
Please let me know the following:
- These accounts that you created to access that datbase, can they connect
and query the database from query analyser?
- Also, could you please created a UDL to test for connectivity (this gives
us a good idea if the odbc connection is ok). Also it could be that we
arent passing the correct 'connection string'?
Create a text file and name it <anything>.UDL. Rt-click the UDL file and
select Properties. The Connection tab contains all the fields exposed to
the application, and you choose the appropriate items to be filled in to
successfully "Test Connection" before you are able to use it. Once the Test
button works, click on OK and open up the UDL file in Notepad. Its just a
text file, and contains a provider string that you should be able to paste
into your application or whichever application is calling the sql server.
Cheers!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.sql

ODBC Connection Problem

Hello,
I have a user who is attempting to connect to SQL Server Enterprise 2000 SP3a running on Windows 2000 Server. The client has WIndows NT 4.0 with MDAC 2.8. The problem is that when I attempt to create the ODBC link , I receive the following error:
"connection failed
sqlstate '01000'
sql server error 770
Microsoft odbc sqlserver driver tcp/ip socket connection write
secencrypt data
Connection failed
sql state '08S01'
Sql server error 18 security error
SSL/Encryption is not forced on the Server and I have many other clients who have no issues in connecting.
If anyone has any suggestions, I would appreciate it.
Thanks
Brent
Run cliconfg.exe. Is "force protocol encryption" checked?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Nope, not ticked.
-- Cindy Gross (MSFT) wrote: --
Run cliconfg.exe. Is "force protocol encryption" checked?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
|||What's the EXACT error message you get and what are you doing when you get
it (secencrypt and 770 are not SQL messages)?
Are there messages in the SQL errorlog or any of the three Windows event
logs on the client or server?
Try each of the following to see if any succeed (if it's a default
instance, ignore the \InstanceName):
np:ServerName\InstanceName
tcp:ServerName\InstanceName
tcp:ServerName\InstanceName, port
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

ODBC Connection Problem

Wednesday, March 21, 2012

ODBC connection failed, but...

Running a database using sql server. User, rights, password set up on the server side and is functioning fine on the users normal desktop. But here is the problem. When the user goes to a different desktop they have never been on before and a new user profile is created on that desktop, they are unable to access the database. When using the front end (access), an odbc connection failure popup occurs when trying to retrieve data. The wierd part is that the odbc connection was set up under admin tools just like it is setup on the working desktop. Also the connection tests successfully. This leads me to believe that it is a desktop side problem and not server side. It is only a problem when a new user profile is setup on a desktop that the user has never been on before.

Any help would be appreciated.

Barry

What is the error the user is getting from the failing desktop - does it say "SQL Server does not exist or access denied"?

Is the SQL Server installed as a default instance listening on the default TCP port 1433 (as opposed to a named instance or a non-default port)?

If the answers are "yes" and "yes", try running

telnet <serverName> 1433

If it reports an error there is an underlying network connectivity problem between the desktop and the server.

If it gives a blank screen the netowkr connectivity works, and further information about your environment and the error may be needed.

You can also follow the checklist posted at

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

|||

Under odbc datasource manager sql setup the libraries is named pipes and the pipes are Serv01and server is serv01, and alias is serv01 as they should be. TCP/IP is Server Serv01 and port is dynamically determined. The exact same setup works under my windows login for myself to access the databse, but will not work when logging in my secretary on my laptop. By the way her name and password are setup correct in the odbc datasource manager. She is able to access the database from her normal workstation though.

The only error we get is odbc connection failure when accessing from the front end at my station. By the way, if I login to her station and go through the sql setup under the odbc manager, I have the same problem at her station that she has at mine.

I'll follow the checklist you posted to see if it helps, but it has to be a local software problem. We'll see though.

Thanks

|||

Nothing like solving your own problem. Well it appears that the naming under the odbc setup manager is highly specific and is not just a name. The problem was that when it asked for a name during setup, "Training Manager" was entered instead of "TrainingManager" without the space. I did not realize that there was connectivity value associated with the name at that point in the setup. The "test connectivity" at the end of the setup will say "successful" with the name written either way, but actually connection to and retrieval of info from the database from front end software will fail.

Hope this helps someone else down the road.

|||

I got the same problem, I am using 2003 server with SQL server 2000. With the same user, I can logon into server but can't create odbc connection beside "SA" login with blank password. This problem happened on our desktop too. Moveover, this problem extend to the application server "Great Plain - Dynamics 9.0". If the user was created on the server side, user can not login properly, unless user's password to be re-entered on the desktop(Windows XP).

I am woundering is the problem caused by encoding issue (Unicode or ASCII). Is there anyone can help?

sql

ODBC -Connection failed

I create a Test database in SQL server, also I add new user in security folder. Also I add this user in Test database, the permission are: Public, and db_owner. But when I let the user try to open the database, it is ODBC Connection failed. I add another user a few months ago, she works fine. But the new user I added in didnt work, did you know why. I am sure I add the same permission as the later user. Thanks.Even though you added the user at the database level and gave it the different access roles (public and db_owner) - make sure you gave the user Database Access to from the Security/Logins dialog box. On the 3rd tab (Database Access) make sure the Test database is checked.

That might be your problem.

Alex|||Alex, it's impossible to have the user be enrolled into db_owner while not having the user to have a Database Access. Something else is fishy/not clear...

Monday, March 19, 2012

ODBC and user accounts

2 questions:
When you set up a new System DSN to a SQL Server db, you have the option of
selecting NT Authentication or SQL authentication.
1. I have been told that Microsoft recommends NT authentication. I haven't
been able to find anything on the MS site or BOL which says so, though.
Anyone got a link I could use which says this?
2. If you use NT authentication, your username (whoever happened to sign
into Windows when creating this DSN) is there, grayed out. Does that matter?
Is that name going to be used in any way when this ODBC connection is used
programatically?
The best forum for this kind of question would be m.p.access.odbcclientsvr
or m.p.access.externaldata.
NT authentication is recommended because the password used doesn't travel in
clear text over the local network. If you have a virus or a trojan on your
LAN, this is the kind of thing that these malwares can catch.
When you use NT authentication, the active current account of the client
machine is always used for connecting to SQL-Server. The username is not
stored, so if you move the MDB file to another machine or if that you use
another account, the new active account will be used instead of the old one.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:OqxxA13eHHA.3960@.TK2MSFTNGP02.phx.gbl...
>2 questions:
> When you set up a new System DSN to a SQL Server db, you have the option
> of selecting NT Authentication or SQL authentication.
> 1. I have been told that Microsoft recommends NT authentication. I haven't
> been able to find anything on the MS site or BOL which says so, though.
> Anyone got a link I could use which says this?
> 2. If you use NT authentication, your username (whoever happened to sign
> into Windows when creating this DSN) is there, grayed out. Does that
> matter? Is that name going to be used in any way when this ODBC connection
> is used programatically?
>
|||> The best forum for this kind of question would be m.p.access.odbcclientsvr
> or m.p.access.externaldata.
Even if it's for SQL Server?
|||Although I am not using Access, your answer most likely stil applies, so
thanks for the explanation.
|||Even if you are using SQL-Server as the backend, your question is only
relevant to the type of client used as the Frontend. Most people using ODBC
links are using Access, hence my suggestion for the forums. However, if you
are using something else as for your frontend, then the best place would be
on a newsgroup about this type of client.
SQL-Server really doesn't care about knowing if the frontend client at the
other side is storing or not the password or if it will reuse the same
username when launched from another machine.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:uZJGnZ4eHHA.2332@.TK2MSFTNGP04.phx.gbl...
> Although I am not using Access, your answer most likely stil applies, so
> thanks for the explanation.
>
|||I am not sure what you mean. I am using SQL Server 2000 only. From the
server, (where my software is running), I set up an ODBC connection,
pointing to the database server. I am not using any front end.
|||You are in the particular case where both the frontend and the backend are
on the same physical machine. However, for the purpose of etablishing an
ODBC connection and reusing or not the same user account when the client
will reetablish later the connection, this doesn't change anything.
In what context are you using this ODBC connection? Are you using it for
etablishing a linked server or if you are using it for another application?
However, if things are currently working properly, then there is no need to
bother yourself with this any more; particularly if you are already using a
Trusted Connection.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:%23$PDWG5eHHA.2640@.TK2MSFTNGP06.phx.gbl...
>I am not sure what you mean. I am using SQL Server 2000 only. From the
>server, (where my software is running), I set up an ODBC connection,
>pointing to the database server. I am not using any front end.
>
|||"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:%23$PDWG5eHHA.2640@.TK2MSFTNGP06.phx.gbl...
>I am not sure what you mean. I am using SQL Server 2000 only. From the
>server, (where my software is running), I set up an ODBC connection,
>pointing to the database server. I am not using any front end.
Disregard the "other forum" suggestion. The basic information provided by
Sylvain applies, regardless of the application using ODBC to connect. It
also applies to any interface used to connect to sql server - odbc, dblib,
ole db, etc.
From a user perspective - the most important reason is that I don't need to
re-enter my security information (userID and password) every time I connect,
nor do I have to manage yet another password to access the dbms.
|||> However, if things are currently working properly, then there is no need
> to bother yourself with this any more;
I'm not bothering myself. I'm only continuing this conversation because you
sent me over to an Access group, when I am not using Access.
|||Hum, you asked in your OP if it does matter to etablish a DSN with or
without NT authentification when this DSN will be used later
programatically.
However, you didn't provide any info about what is exactly this
programmation; so the exact answer to your original question is *Yes*: it
Does matter because you can use this DSN to reconstruct a new DSN or to
etablish a DSN-Less connection with or without the same parameters.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:OBP9yB6eHHA.3948@.TK2MSFTNGP03.phx.gbl...
> I'm not bothering myself. I'm only continuing this conversation because
> you sent me over to an Access group, when I am not using Access.
>

ODBC and user accounts

2 questions:
When you set up a new System DSN to a SQL Server db, you have the option of
selecting NT Authentication or SQL authentication.
1. I have been told that Microsoft recommends NT authentication. I haven't
been able to find anything on the MS site or BOL which says so, though.
Anyone got a link I could use which says this?
2. If you use NT authentication, your username (whoever happened to sign
into Windows when creating this DSN) is there, grayed out. Does that matter?
Is that name going to be used in any way when this ODBC connection is used
programatically?The best forum for this kind of question would be m.p.access.odbcclientsvr
or m.p.access.externaldata.
NT authentication is recommended because the password used doesn't travel in
clear text over the local network. If you have a virus or a trojan on your
LAN, this is the kind of thing that these malwares can catch.
When you use NT authentication, the active current account of the client
machine is always used for connecting to SQL-Server. The username is not
stored, so if you move the MDB file to another machine or if that you use
another account, the new active account will be used instead of the old one.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:OqxxA13eHHA.3960@.TK2MSFTNGP02.phx.gbl...
>2 questions:
> When you set up a new System DSN to a SQL Server db, you have the option
> of selecting NT Authentication or SQL authentication.
> 1. I have been told that Microsoft recommends NT authentication. I haven't
> been able to find anything on the MS site or BOL which says so, though.
> Anyone got a link I could use which says this?
> 2. If you use NT authentication, your username (whoever happened to sign
> into Windows when creating this DSN) is there, grayed out. Does that
> matter? Is that name going to be used in any way when this ODBC connection
> is used programatically?
>|||> The best forum for this kind of question would be m.p.access.odbcclientsvr
> or m.p.access.externaldata.
Even if it's for SQL Server?|||Although I am not using Access, your answer most likely stil applies, so
thanks for the explanation.|||Even if you are using SQL-Server as the backend, your question is only
relevant to the type of client used as the Frontend. Most people using ODBC
links are using Access, hence my suggestion for the forums. However, if you
are using something else as for your frontend, then the best place would be
on a newsgroup about this type of client.
SQL-Server really doesn't care about knowing if the frontend client at the
other side is storing or not the password or if it will reuse the same
username when launched from another machine.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:uZJGnZ4eHHA.2332@.TK2MSFTNGP04.phx.gbl...
> Although I am not using Access, your answer most likely stil applies, so
> thanks for the explanation.
>|||I am not sure what you mean. I am using SQL Server 2000 only. From the
server, (where my software is running), I set up an ODBC connection,
pointing to the database server. I am not using any front end.|||You are in the particular case where both the frontend and the backend are
on the same physical machine. However, for the purpose of etablishing an
ODBC connection and reusing or not the same user account when the client
will reetablish later the connection, this doesn't change anything.
In what context are you using this ODBC connection? Are you using it for
etablishing a linked server or if you are using it for another application?
However, if things are currently working properly, then there is no need to
bother yourself with this any more; particularly if you are already using a
Trusted Connection.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:%23$PDWG5eHHA.2640@.TK2MSFTNGP06.phx.gbl...
>I am not sure what you mean. I am using SQL Server 2000 only. From the
>server, (where my software is running), I set up an ODBC connection,
>pointing to the database server. I am not using any front end.
>|||"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:%23$PDWG5eHHA.2640@.TK2MSFTNGP06.phx.gbl...
>I am not sure what you mean. I am using SQL Server 2000 only. From the
>server, (where my software is running), I set up an ODBC connection,
>pointing to the database server. I am not using any front end.
Disregard the "other forum" suggestion. The basic information provided by
Sylvain applies, regardless of the application using ODBC to connect. It
also applies to any interface used to connect to sql server - odbc, dblib,
ole db, etc.
From a user perspective - the most important reason is that I don't need to
re-enter my security information (userID and password) every time I connect,
nor do I have to manage yet another password to access the dbms.|||> However, if things are currently working properly, then there is no need
> to bother yourself with this any more;
I'm not bothering myself. I'm only continuing this conversation because you
sent me over to an Access group, when I am not using Access.|||Hum, you asked in your OP if it does matter to etablish a DSN with or
without NT authentification when this DSN will be used later
programatically.
However, you didn't provide any info about what is exactly this
programmation; so the exact answer to your original question is *Yes*: it
Does matter because you can use this DSN to reconstruct a new DSN or to
etablish a DSN-Less connection with or without the same parameters.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:OBP9yB6eHHA.3948@.TK2MSFTNGP03.phx.gbl...
>> However, if things are currently working properly, then there is no need
>> to bother yourself with this any more;
> I'm not bothering myself. I'm only continuing this conversation because
> you sent me over to an Access group, when I am not using Access.
>|||I think my question was misunderstood.
1. Is there an official Microsoft recommendation to use NT Authentication
rather than SQL Server authentication on the ODBC connection itself. The
answer is Yes, which I found after I posted.
2. When you choose NT authentication, the box at the bottom of that same
screen, where you would type in your username/password, is grayed out. But
you can still see the text of your login name. If grayed out, does this
login name get used in any way by the ODBC connection?|||« If grayed out, does this login name get used in any way by the ODBC
connection? »
No, any user name stored in a DSN will not be used when etablishing a
trusted connection; the current active account will always be used.
If you want to, you can change the rules - for exemple switching the DSN
from a trusted connection a SQL account; however, this is probably not what
you have in mind, otherwise, you wouldn't ask the question here.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:eJAR8$6eHHA.284@.TK2MSFTNGP05.phx.gbl...
>I think my question was misunderstood.
> 1. Is there an official Microsoft recommendation to use NT Authentication
> rather than SQL Server authentication on the ODBC connection itself. The
> answer is Yes, which I found after I posted.
> 2. When you choose NT authentication, the box at the bottom of that same
> screen, where you would type in your username/password, is grayed out. But
> you can still see the text of your login name. If grayed out, does this
> login name get used in any way by the ODBC connection?
>|||Thanks!
> No, any user name stored in a DSN will not be used when etablishing a
> trusted connection; the current active account will always be used.

ODBC and user accounts

2 questions:
When you set up a new System DSN to a SQL Server db, you have the option of
selecting NT Authentication or SQL authentication.
1. I have been told that Microsoft recommends NT authentication. I haven't
been able to find anything on the MS site or BOL which says so, though.
Anyone got a link I could use which says this?
2. If you use NT authentication, your username (whoever happened to sign
into Windows when creating this DSN) is there, grayed out. Does that matter?
Is that name going to be used in any way when this ODBC connection is used
programatically?The best forum for this kind of question would be m.p.access.odbcclientsvr
or m.p.access.externaldata.
NT authentication is recommended because the password used doesn't travel in
clear text over the local network. If you have a virus or a trojan on your
LAN, this is the kind of thing that these malwares can catch.
When you use NT authentication, the active current account of the client
machine is always used for connecting to SQL-Server. The username is not
stored, so if you move the MDB file to another machine or if that you use
another account, the new active account will be used instead of the old one.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:OqxxA13eHHA.3960@.TK2MSFTNGP02.phx.gbl...
>2 questions:
> When you set up a new System DSN to a SQL Server db, you have the option
> of selecting NT Authentication or SQL authentication.
> 1. I have been told that Microsoft recommends NT authentication. I haven't
> been able to find anything on the MS site or BOL which says so, though.
> Anyone got a link I could use which says this?
> 2. If you use NT authentication, your username (whoever happened to sign
> into Windows when creating this DSN) is there, grayed out. Does that
> matter? Is that name going to be used in any way when this ODBC connection
> is used programatically?
>|||> The best forum for this kind of question would be m.p.access.odbcclientsvr
> or m.p.access.externaldata.
Even if it's for SQL Server?|||Although I am not using Access, your answer most likely stil applies, so
thanks for the explanation.|||Even if you are using SQL-Server as the backend, your question is only
relevant to the type of client used as the Frontend. Most people using ODBC
links are using Access, hence my suggestion for the forums. However, if you
are using something else as for your frontend, then the best place would be
on a newsgroup about this type of client.
SQL-Server really doesn't care about knowing if the frontend client at the
other side is storing or not the password or if it will reuse the same
username when launched from another machine.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:uZJGnZ4eHHA.2332@.TK2MSFTNGP04.phx.gbl...
> Although I am not using Access, your answer most likely stil applies, so
> thanks for the explanation.
>|||I am not sure what you mean. I am using SQL Server 2000 only. From the
server, (where my software is running), I set up an ODBC connection,
pointing to the database server. I am not using any front end.|||You are in the particular case where both the frontend and the backend are
on the same physical machine. However, for the purpose of etablishing an
ODBC connection and reusing or not the same user account when the client
will reetablish later the connection, this doesn't change anything.
In what context are you using this ODBC connection? Are you using it for
etablishing a linked server or if you are using it for another application?
However, if things are currently working properly, then there is no need to
bother yourself with this any more; particularly if you are already using a
Trusted Connection.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:%23$PDWG5eHHA.2640@.TK2MSFTNGP06.phx.gbl...
>I am not sure what you mean. I am using SQL Server 2000 only. From the
>server, (where my software is running), I set up an ODBC connection,
>pointing to the database server. I am not using any front end.
>|||"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:%23$PDWG5eHHA.2640@.TK2MSFTNGP06.phx.gbl...
>I am not sure what you mean. I am using SQL Server 2000 only. From the
>server, (where my software is running), I set up an ODBC connection,
>pointing to the database server. I am not using any front end.
Disregard the "other forum" suggestion. The basic information provided by
Sylvain applies, regardless of the application using ODBC to connect. It
also applies to any interface used to connect to sql server - odbc, dblib,
ole db, etc.
From a user perspective - the most important reason is that I don't need to
re-enter my security information (userID and password) every time I connect,
nor do I have to manage yet another password to access the dbms.|||> However, if things are currently working properly, then there is no need
> to bother yourself with this any more;
I'm not bothering myself. I'm only continuing this conversation because you
sent me over to an Access group, when I am not using Access.|||Hum, you asked in your OP if it does matter to etablish a DSN with or
without NT authentification when this DSN will be used later
programatically.
However, you didn't provide any info about what is exactly this
programmation; so the exact answer to your original question is *Yes*: it
Does matter because you can use this DSN to reconstruct a new DSN or to
etablish a DSN-Less connection with or without the same parameters.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Middletree" <middletree@.hottttttttmail.com> wrote in message
news:OBP9yB6eHHA.3948@.TK2MSFTNGP03.phx.gbl...
> I'm not bothering myself. I'm only continuing this conversation because
> you sent me over to an Access group, when I am not using Access.
>

Friday, March 9, 2012

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.

Wednesday, March 7, 2012

Obtaining Audit Information

Hi People

We want one trigger which captures the data as mentioned below.

Table Name

Date and Time

User

type

Mode of modification

TABLE_ABC

03/07/2007 12:00:04

XCS\Raoa

Update

Procedure

TABLE_DEF

03/07/2007 12:00:34

XCS\Raoa

Insert

Class Integration SSIS Package

TABLE_GHI

03/07/2007 12:01:04

XCS\Raoa

Insert

Procedure

TABLE_GHI

03/07/2007 12:01:34

XCS\Raoa

Update

XCS\Raoa (Manual)

I am not sure about how to achieve the last column. I hope u understand what exactly is expected. The idea is that, one should be able to track how a particular table was manipulate; whether it was manipulated using procedure or SSIS package or manually.

Could someone help me achieving this?

Regards

Abhi

Hi Abhi,

I think you are looking for a DDL trigger. Here is my example. We are monitoring the changes on views, procedures, etc. We are using this:

Code Snippet

USE [databasename]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[T_DDL_DDLEventLog](

[EventDate] [datetime] NOT NULL,

[UserName] [sysname] NOT NULL,

[ObjectName] [sysname] NOT NULL,

[CommandText] [varchar](max) NOT NULL

) ON [PRIMARY]

SET ANSI_PADDING OFF

USE [databasename]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TRIGGER [DDL_Alter_Audit]

ON DATABASE

FOR ALTER_TABLE, ALTER_PROCEDURE, ALTER_FUNCTION, ALTER_VIEW, ALTER_TRIGGER

DECLARE @.eventData XML

SET @.eventData = eventdata()

INSERT T_DDL_DDLEventLog (EventDate, UserName, ObjectName, CommandText)

SELECT

GETDATE() AS EventDate,

@.eventData.value('data(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME')

AS UserName,

@.eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

AS ObjectName,

@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)') AS CommandText

SET ANSI_NULLS OFF

SET QUOTED_IDENTIFIER OFF

ENABLE TRIGGER [DDL_Alter_Audit] ON DATABASE

Also, you can find more informationabout DDL triggers in the BOL.

I hope it helps.

Regards,

Janos

|||

Thanks for replying Janos.

What does following statement return?

@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)') AS CommandText

Anyways, I m looking for DML triggers. Whenever someone inserts/updates the table, I wanna know how that table was manipulated..was it manipulated using SSIS package or stored procedure or manually by some user. Is it possible to capture this information

|||

Hi,

CommandText returns the T-SQL script ran against the object. Eg.: In got a table called Table1. when I'm going to alter it, the Command text will contain the altering sql script, like ALTER TABLE Table1 .....

LoginName will return the credentila used to make the alter on the table and the ObjectName return Table1, in this case.

You can make this DDL trigger for all database events with DDL_DATABASE_LEVEL_EVENTS event, but this still monitors the objects, not monitoring your DML actions. You should write DML triggers for all your tables required audit.

Regards,

Janos

|||

Thanks for you reply Janos.. Cheers!!!

Obtaining Audit Information

Hi People

We want one trigger which captures the data as mentioned below.

Table Name

Date and Time

User

type

Mode of modification

TABLE_ABC

03/07/2007 12:00:04

XCS\Raoa

Update

Procedure

TABLE_DEF

03/07/2007 12:00:34

XCS\Raoa

Insert

Class Integration SSIS Package

TABLE_GHI

03/07/2007 12:01:04

XCS\Raoa

Insert

Procedure

TABLE_GHI

03/07/2007 12:01:34

XCS\Raoa

Update

XCS\Raoa (Manual)

Could someone help me achieving this?

Regards

Abhi

Hi Abhi,

I think you are looking for a DDL trigger. Here is my example. We are monitoring the changes on views, procedures, etc. We are using this:

Code Snippet

USE [databasename]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[T_DDL_DDLEventLog](

[EventDate] [datetime] NOT NULL,

[UserName] [sysname] NOT NULL,

[ObjectName] [sysname] NOT NULL,

[CommandText] [varchar](max) NOT NULL

) ON [PRIMARY]

SET ANSI_PADDING OFF

USE [databasename]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TRIGGER [DDL_Alter_Audit]

ON DATABASE

FOR ALTER_TABLE, ALTER_PROCEDURE, ALTER_FUNCTION, ALTER_VIEW, ALTER_TRIGGER

DECLARE @.eventData XML

SET @.eventData = eventdata()

INSERT T_DDL_DDLEventLog (EventDate, UserName, ObjectName, CommandText)

SELECT

GETDATE() AS EventDate,

@.eventData.value('data(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME')

AS UserName,

@.eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

AS ObjectName,

@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)') AS CommandText

SET ANSI_NULLS OFF

SET QUOTED_IDENTIFIER OFF

ENABLE TRIGGER [DDL_Alter_Audit] ON DATABASE

Also, you can find more informationabout DDL triggers in the BOL.

I hope it helps.

Regards,

Janos

|||

Thanks for replying Janos.

What does following statement return?

@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)') AS CommandText

|||

Hi,

LoginName will return the credentila used to make the alter on the table and the ObjectName return Table1, in this case.

Regards,

Janos

|||

Thanks for you reply Janos.. Cheers!!!

Friday, February 24, 2012

Obtain values from different tables

Table processes_user
id_user | id_proc
--
35 | 17001
100 | 1089
35 | 17002
Table processes_flow
(example:the process 17001 is with the user 35 and the deadline of the
flow is 2006-07-30! As you can see there are 2 entries on the table
below, the first with a lower id references an old flow, but I want to
get the deadline of the last flow of the process)
--
id | id_proc | deadline
--
10| 17001 | null
12| 1089 | 2006-05-12
15| 17001 | 2006-07-30
20| 17002 | null
--
I would like to get for the user 35 the following info:
17001 | 2006-07-30
17002 | null
How can I do this with a sql command? I would like you to share some
ideas because I'm stuck with this.
Regards,
Hugo SantosSELECT a.id_user, a.id_proc, b.deadline
FROM processes_user a
LEFT JOIN (SELECT id_proc, MAX(deadline) as deadline
FROM processes_flow
GROUP BY id_proc) b ON a.id_proc = b.id_proc
WHERE a.id_user= 35
Untested.
Stu
hugonsan...@.gmail.com wrote:
> Table processes_user
> id_user | id_proc
> --
> 35 | 17001
> 100 | 1089
> 35 | 17002
>
> Table processes_flow
> (example:the process 17001 is with the user 35 and the deadline of the
> flow is 2006-07-30! As you can see there are 2 entries on the table
> below, the first with a lower id references an old flow, but I want to
> get the deadline of the last flow of the process)
> --
> id | id_proc | deadline
> --
> 10| 17001 | null
> 12| 1089 | 2006-05-12
> 15| 17001 | 2006-07-30
> 20| 17002 | null
> --
> I would like to get for the user 35 the following info:
> 17001 | 2006-07-30
> 17002 | null
> How can I do this with a sql command? I would like you to share some
> ideas because I'm stuck with this.
> Regards,
> Hugo Santos|||Stu wrote:
> SELECT a.id_user, a.id_proc, b.deadline
> FROM processes_user a
> LEFT JOIN (SELECT id_proc, MAX(deadline) as deadline
> FROM processes_flow
> GROUP BY id_proc) b ON a.id_proc = b.id_proc
> WHERE a.id_user= 35
>
> Untested.
> Stu
>
Thanks for your reply Stu, but with that aren't you going to get the
max deadline only? I want to show the deadline from the last flow.
For example.. on the first flow you may have a deadline and on the
second the deadline is null. And I want to get the deadline from the
last flow... which is null.
Can you clear this out?|||I'm sorry, I made an assumption that may or may not be true; I'm
assuming that NULL comes before a deadline. In that case, the subquery
will only return rows that have a deadline associated with them; doing
a LEFT JOIN will return the MAX(deadline) if there is one, and NULL if
there is not.
hugos wrote:
> Stu wrote:
> Thanks for your reply Stu, but with that aren't you going to get the
> max deadline only? I want to show the deadline from the last flow.
> For example.. on the first flow you may have a deadline and on the
> second the deadline is null. And I want to get the deadline from the
> last flow... which is null.
> Can you clear this out?|||Thanks.
I already found a different way! I think it's not optimized but it will
work for now ;)
Stu escreveu:
> I'm sorry, I made an assumption that may or may not be true; I'm
> assuming that NULL comes before a deadline. In that case, the subquery
> will only return rows that have a deadline associated with them; doing
> a LEFT JOIN will return the MAX(deadline) if there is one, and NULL if
> there is not.

Friday, March 30, 2012

Odbc error

ODBC DSN for SQL Database

ODBC DSN for SQL Database

ODBC DSN for SQL Database

Wednesday, March 28, 2012

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

Monday, March 26, 2012

ODBC Connection with SQL Express

Friday, March 23, 2012

ODBC Connection Problem

ODBC Connection Problem

ODBC Connection Problem

ODBC Connection Problem

Wednesday, March 21, 2012

ODBC connection failed, but...

ODBC -Connection failed

Monday, March 19, 2012

ODBC and user accounts

ODBC and user accounts

ODBC and user accounts

Friday, March 9, 2012

Obtaining Window login name from a trigger

Obtaining Window login name from a trigger

Wednesday, March 7, 2012

Obtaining Audit Information

Obtaining Audit Information

Friday, February 24, 2012

Obtain values from different tables

Objects related to DB

Blog Archive

About Me