SQL Connection: Integrated Security setting and how info on stored procedure is pulled

Data Abstract
1

Traditionally I have always used our SQL connection with Integrated Security=SSPI. When I run the following command:

KeySP := Connection.NewCommand(‘GetUniqueKey’, stStoredProcedure) ;

I can see in the SQL Profiler the following command:

exec sp_sproc_columns N’GETUNIQUEKEY’, N’DBO’, N’COMSYS’, NULL, @ODBCVer = 3

And you will see this command ran with the NTUsername of the administrator (this is a server) and the LoginName says DOMAIN\administrator.

You will notice the second parameter of the sp_sproc_columns is the owner parameter and what is passed is the ‘DBO’. This works and the program gets the sp parameters.

I do have login name and password in the connection string which is not the administrator, but a specific account… but I guess the Integrated Security=SSPI overrides and uses NT authenticated user of the service running this the login name doesn’t show up in the profiler… just the administrator which is what the service runs as.

HOWEVER… if I change integrated security to false I see it does start using the login name I passed, BUT it makes another change which leads to problems. The command above changes to this:

exec sp_sproc_columns N’GETUNIQUEKEY’, N’MYLOGINNAME’, N’COMSYS’, NULL, @ODBCVer = 3

The second parameter now shows the login name and not dbo which then never returns anything since the owner of these objects is DBO.

Why am I looking to NOT use SSPI? Because we have views we need to query from this service which have security based on the actually logged in user which the view utilizes. And I need to login as the user and not use the NT authority of the service. I have can definitely use there login, but as you can see the sp_sproc_columns command changes which then leads to issues.

How have others dealt with this? Is there a way to either have it pass in NULL (which works) or always be “DBO”.

Greg

2

Hello Greg,

Could you give me connection strings for your two cases?

Best regards

3

Tried to show .daConnections file here, but the XML nature of it wasn’t looking correct in comment. So attaching a text file of it.

Let me know if you needed something else. Not sure if the command
KeySP := Connection.NewCommand(‘GetUniqueKey’, stStoredProcedure) ;
uses the connection loaded in the TDAConnectionManager or if it some how ties back to a TADOConnection object. There is a TADOConnection object on the fServerDataModule with the TDAConnectionManager, but I haven’t messed with this thing in so long I am not sure what the TADOConnection object is there for.

Greg

4

Hello,

Could you check please for your user account User Mapping for necessary database (COMSYS) in SQL Management Studio. Seems like sp_sproc_columns takes second parameter from User Mapping Default Schema for appropriate database.

If you have ‘dbo’ Default Schema for you account User Mapping, and nevertheless sp_sproc_columns is executed with ‘MYLOGINNAME’ second parameter, please let us know what SQL server version you use, just in case indicate DA and Delphi versions you use too. Also show us all properties of your non-admin SQL account, maybe do screenshots from SQL Management Studio.

There is a TADOConnection object on the fServerDataModule with the TDAConnectionManager, but I haven’t >messed with this thing in so long I am not sure what the TADOConnection object is there for.

DA doesn’t add any TADOConnection components in it’s projects. Read more here Documentation | RemObjects Software

Best regards

5

Elenap,

In this case it is SQL 2000.

Are you talking about the SQL Server Login Properties / Database Access tab where it has the Permit column, Database column and User column?

If so one entry has permit checked, Database as COMSYS, user is MYLOGINNAME.

If you are talking about something else could you direct me.

In regards to TADOConnection. I can’t remember why I had to add it. I recall that DA should not need it, but there was something that forced me to add it before things worked. Maybe it was to get a unit to pull into the uses clause. I’ll have to figure that out later.

6

Hello,

In this case it is SQL 2000.

Are you talking about the SQL Server Login Properties / Database Access tab where it has the Permit column, >Database column and User column?

I was talking about ‘Default Schema’ in MSSQL 2008. There is no schema concept in SQL 2000.

You should add owner name before procedure name:
KeySP := Connection.NewCommand(‘dbo.GetUniqueKey’, stStoredProcedure) ;

You could also add alias for your ‘MYLOGINNAME’ account in COMSYS database on the SQL server:

sp_addalias ‘MYLOGINNAME’, ‘dbo’

Database Account sheet of Logins properties for ‘MYLOGINNAME’ login should not contain permissions for COMSYS database. By other words you should delete User ‘MYLOGINNAME’ from COMSYS database before adding alias for it.

Best regards