NT Integrated security VS Specific User name and password

[Bob McClellan]

I have an app that is .adp (2003/2007) front end to sql2005 (Express) back
end.

I see a significant perfomance hit when I switch between use 'sa' and
password compared
to Windows NT Integrated security. Everything works. Permissions are all
good as far as I can
tell.

Can anyone point me in the right direction with regard to where I should
start looking to solve
this problem. I really want the users to be able to land at the Main Menu
when they open the app
as opposed to logging in through an Initial 'Log In' form.

thanks in advance,
...bob

 

[Paul Shapiro]

If you mean that it works better when all users login as SA, you probably
don't want users to have that much db privilege. Do all of your sql object
references include the dbo. schema qualifier, assuming all your objects are
in the dbo schema?

So it would be Select ... From dbo.MyTable rather than From MyTable.

 

[Bob McClellan]

Paul,
I definetly do not want users logging in as sa.
That's the reason for the post.
I'm trying to understand why the .adp performs faster when logged
in as sa as opposed to with NT authentication.
Your point about referencing all tables with the dbo schema is a place
to start. There are two user specific schemas and the users are in roles
with permission to those schemas. I will check to confirm there are no
stored procedures that do not reference tables without the schema.
Thanks for the reply and the advice.
...bob

 

[Sylvain Lafontaine]

This smell like a bad query plan. You should check your query plans when
the users log with NT authentification. One quick check would be to use the
WITH RECOMPILE option for your stored procedures and search Google for «
sql-server "parameter sniffing" OR "parameters sniffing" ».

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Bob McClellan]

While in the .adp, and doing nothing other than setting the connection
properties,
if you set it to sa and click test, it returns instantly with - test
connection succeeded . If I switch
to NT authentication and click test, it takes 12+ seconds to come back as -
test connection succeeded.
This has nothing to do with any stored procedures. I just do not understand
how the authentication is
taking so long to authenticate.

 

[Bob McClellan]

Additionally..
It does not even have to be 'sa'.
I created a user with the same name as the authenticated user.
testing with this username also
returns instantly with - test connection succeeded.

 

[Bob McClellan]

Sylvain & Paul,
There is a problem with the server. I copied the .mdf to another box and
attached it.
Running the same tests, pointing to the IP on the 2nd machine returns
instantly
with NT Authentication. Thanks for the replies.
...bob

 

[Sylvain Lafontaine]

You seem to think that I'm sitting in front of your desk. I am not and I
cannot make any test or verification or anything else than offering you some
suggestions.

The first thing to do when you have a speed problem between accounts is to
eliminate the possibility of a bad query plan by using the option WITH
RECOMPILE to the stored procedure that you are testing and search Google for
« sql-server "parameter sniffing" OR "parameters sniffing" ».

If this doesn't turn up anything, you should clear all the buffers
(especially DBCC FREEPROCCACHE) and try to verify your query plans using
the SQL-Server Profiler but this is a little more complicated to do than the
previous suggestions, particularly if you're not well accustomed with the
profiler.

Finally, there have been many complaints about the speed of ADP 2007 in the
past. Myself, I don't use it and none of my clients neither. As you are
also using ADP 2003, I don't know if anything of these apply but still, you
should check the thread about "About 2007 / SQL 2008 slow to open" on
2009-11-04 for a possible solution (setting the PUBLIC database role rights
to the database) and also searching the internet for Access/ADP 2007 and
speed problems.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Sylvain Lafontaine]

Most likely, there is no problem with the server but you have a problem with
a bad query plan. This is a very common problem. Clear the procedure cache
with DBCC FREEPROCCACHE and try adding the option WITH RECOMPILE for the
stored procedure and search the internet for « sql-server "parameter
sniffing" OR "parameters sniffing" ».

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Bob McClellan]

This post came in after my last one Sylvain. I did not see your post until
afterward.
Please know I appreciate your suggestions and do not think
you are "sitting in front of my desk". I was simply trying to elaborate on
the problem being with the actual connection as opposed
to a security issue between user / role / schema. I develop in 2007 as I
prefer
the UI but most of the apps I role out are to clients with 2003. I'm aware
of the
issues that have been brought up about speed with 2007 .adp apps. I have
not
found any issues so far. I use stored procedures as the record source for
all forms
and change the inputparameters on subforms dynamically via vba. As long as
the
tables are indexed correctly, the speed is great.
...anyway, I just wanted to make sure to say thanks for the reply and the
advice.
I really do appreciate it.
...bob

 

[Sylvain Lafontaine]

Bob, after a second and close reading of your previous messages, I can now
see that you say that you have a connection problem even without calling any
stored procedure or select queries and obviously, this has nothing to do
with query plans. But sadly, I don't have a solution that come to my mind
at this moment. Without seeing the overall setting of the permissions, this
could be anything. Make sure that your users have their GRANT VIEW
DEFINITION set and that they are member of the public Role for the database.

If possible, try using another protocol (ie., if you are using named pipes,
switch to tcp/ip and vice-versa.).

Did you check the log files of SQL-Server to see if there are nothing out of
the ordinary there?

Finally, check if this happens only with ADP or if it happens with other
query tools as well. If I were you, I would post in a newsgroup dedicated to
SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Paul Shapiro]

Glad you got it resolved. Maybe check the DNS and other network settings on
the problematic server. Are these computers in a domain, or is it a
workgroup? If the SQL Server accounts connected instantly but not domain
accounts, maybe that server is taking too long to contact the domain server.

 

[Bob McClellan]

Thanks Paul,
The machine is on a Domain.
Last night I completely uninstalled the SQLEXPRESS SP3 from the
development server (that's the machine with the problem).
I then reinstalled it (SQLEXPRESS SP3) .
It now connects instantly with the server name\ServerInstance.
If Itry IP\ServerInstance it is still taking longer.
I can use the servername\serverInstance. This gets me out of the jam.
I really want to know what's up on that server though. I'll be touching
base with the guy who handles the network for them.
Thanks again for the reply and the advice.
...bob