map logins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

map logins

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-22 : 18:48:42

Hi friends
We have small application i.e Tracker(VFP8 and sql server) in our development which tracks
our daily tasks and assignments.
during the day we open many other applications which also expect a userid and pwd to login like Tracker.
sometimes it becomes so tidious entering those login ids.
so i want implement automatic login for Tracker.
I know that i could use windows authentication for login.but login id is key in our database.so windows login
may not be same as database's login id
so how can i map these login s
for example my windows login is work
but login for Tracker is rk(it will be in staff table) so when i login using work it should map to login rk and get all relevant tasks of mine
any ideas please
Thanks

Cheers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-22 : 18:51:23

But why can't you use Windows Authentication? Don't use work or rk, use your domain account. Have a central database that all apps can connect to that stores user information. One of the tables would contains the Windows accounts allowed in the app.

Windows Authentication is the way to go here. Don't use a bandaid approach. Do it right now that you see a problem.

Tara

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-22 : 18:54:23

Thanks for ur post Tara.Actually we created our application using sql logins and this login key fields in our data tables.our latest requirement expect automatic login.I know i could use windows login but the problem is it may not get data i.e necessary bcoz of loginid.

Cheers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-22 : 18:57:47

What do you mean it may not get data i.e necessary bcoz of loginid? Automatic login wouldn't be a problem with Windows Authentication either since it would use the credentials of the person currently logged in.

Tara

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-22 : 19:12:25

ok may b i need b more specific.
There r 2 main tables staff and tasks.
The foreign key between them is staffid(i.e our actual login id)so when someone logins the application gets all tasks of logged in user from tasks table.
now if we use windows authentication (which may not be same as staff.staffid) we dont get relavant tasks from Tasks table.
hope this is clear

Cheers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-22 : 19:14:28

Does the Windows Authentication account have permissions to view the data?

Tara

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-22 : 19:15:11

Yes.

Cheers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-22 : 19:17:16

We would really need to see the data, query, and schema in order to help out here.

Tara

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-22 : 19:25:54

Here fk_staff id actual login id..

CREATE TABLE [dbo].[Taskgroup] (
[fk_staffid] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fk_taskid] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[taskowner] [bit] NULL ,
[rowactive] [tinyint] NOT NULL ,
[rowid] [row_id] NULL ,
[unqid] [unq_id] NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Taskgroup] WITH NOCHECK ADD
CONSTRAINT [PK_Staffgroup] PRIMARY KEY CLUSTERED
(
[fk_staffid],
[fk_taskid]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Taskgroup] ADD
CONSTRAINT [FK_Staffgroup_Staff] FOREIGN KEY
(
[fk_staffid]
) REFERENCES [dbo].[Staff] (
[StaffId]
),
CONSTRAINT [FK_Staffgroup_Task] FOREIGN KEY
(
[fk_taskid]
) REFERENCES [dbo].[Task] (
[Taskid]
)
GO

This is actual staff table
CREATE TABLE [dbo].[Staff] (
[StaffId] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Staffname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StaffPriority] [tinyint] NOT NULL ,
[Staffemail] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fk_typeid] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[quota1] [tinyint] NULL ,
[quota2] [tinyint] NULL ,
[quota3] [tinyint] NULL ,
[quota4] [tinyint] NULL ,
[quota5] [tinyint] NULL ,
[rowid] [row_id] NULL ,
[unqid] [unq_id] NULL ,
[lpassword] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rowactive] [tinyint] NOT NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Staff] WITH NOCHECK ADD
CONSTRAINT [pk_staffid] PRIMARY KEY CLUSTERED
(
[StaffId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Staff] ADD
CONSTRAINT [FK_Stafftype_Staff] FOREIGN KEY
(
[fk_typeid]
) REFERENCES [dbo].[Stafftype] (
[typeid]
)
GO

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-22 : 19:30:35

This is actual connection string
lcConnStr="driver=SQL Server;";
+"server="+STRTRAN(gcServer,CHR(13),'')+";uid="+lcUserID+";pwd="+lcPassword;
+";database="+STRTRAN(gcDB,CHR(13),'')+";dsn="

* Turning off ODBC login display for connection
=SQLSetProp(0,"DispLogin",3)
* set connection timeout for connections
=SQLSetProp(0,"ConnectTimeOut",gcTout)

*!* now create a connection
goConn=SQLSTRINGCONNECT(lcConnStr)

IF goConn<0
RETURN thisform.invalidlogin("Invalid Login ID/Password ....")
ELSE ok,we r connected
*!* store staff id in global variable
gcStaffid =lcUserID
ENDIF

after valid login we call form tasks and its form load there is stored proc
i.e

lnStatus=SQLEXEC(goconn,"EXEC pt_ExploreTasks;2 6,?lcCatid,?lcstaff",'curdummy')

here the parameter lcstaff is login id taken from global var i.e gcStaffid.and abv proc gets all tasks of logged in user

Cheers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-22 : 19:31:23

So does the Windows Authentication account exist in the Staff table? Is the application passing in the account as DomainName\AccountName? If so and that combo doesn't exist in the staff table, then that's your problem. A sql account will be AccountName while Windows account will be DomainName\AccountName.

Tara

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-22 : 19:33:22

----
So does the Windows Authentication account exist in the Staff table?
---
actually i've not implemented windows authentication option.i am wondering best way to handle this situation.

Cheers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-22 : 19:35:25

But you said this:

"now if we use windows authentication (which may not be same as staff.staffid) we dont get relavant tasks from Tasks table."

So how do you know that you won't get relevant tasks?

Your data will need to support the fact that you are using Windows Authentication or your application will need to pass only the account name and not domainname\accountname.

Tara

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-22 : 19:37:12

--
So how do you know that you won't get relevant tasks?
--
bcoz my windows login is rajani but staff.staffid is rk
so obviously if i login using windows authentication i dont get 2 see all of my tasks bcoz staff id does not match

Cheers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-22 : 19:39:34

You'll have to modify data. But you should do it regardless of what a pain it might be, it's the right thing to do.

Tara

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-22 : 19:42:27

Thanks Tara.i am planning to create new field in staff table that stores
windows login.and i'll create new option in user preferences (in our application) where user can map his sql login to a windows loggin
so i could use when he logins using windows authentication.
what do u think?

Cheers

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-22 : 19:49:06

The best way to change it is to redo the table and just use Windows Authentication. In other words, replace the current login information. If this is not possible, and there are cases when it is not if you are validating internet users or the management will not let you change database functionality, then you need to add a mapping column called nt_username in the staff table.

You can then change your connection logic to test for successful NT authentication first. If it's not available request username and password. This will allow you to do a gradual global conversion.

Just something to think about. The way you are set up now though, you will either have to change your structure, or build the work-around and do a gradual conversion.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-22 : 19:54:41

Thanks Derrick
Thats a start

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources