Connection pooling and Windows authentication

Hello:
I am correct that if I use Windows authentication on SQL2000 I will loose
connection pooling ?
Thanks
Terry
Not exactly; connections will not be pooled between accounts, but I believe
that if two connections are made with the exact same security credentials,
pooling will work.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Support" <RemoveThis_Support@.mail.oci.state.ga.us> wrote in message
news:%23qgxFtKzEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Hello:
> I am correct that if I use Windows authentication on SQL2000 I will loose
> connection pooling ?
> Thanks
> Terry
>

Connection pooling and Windows authentication

Hello:
I am correct that if I use Windows authentication on SQL2000 I will loose
connection pooling ?
Thanks
TerryNot exactly; connections will not be pooled between accounts, but I believe
that if two connections are made with the exact same security credentials,
pooling will work.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Support" <RemoveThis_Support@.mail.oci.state.ga.us> wrote in message
news:%23qgxFtKzEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Hello:
> I am correct that if I use Windows authentication on SQL2000 I will loose
> connection pooling ?
> Thanks
> Terry
>sqlsql

Connection pooling and Windows authentication

Hello:
I am correct that if I use Windows authentication on SQL2000 I will loose
connection pooling ?
Thanks
TerryNot exactly; connections will not be pooled between accounts, but I believe
that if two connections are made with the exact same security credentials,
pooling will work.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Support" <RemoveThis_Support@.mail.oci.state.ga.us> wrote in message
news:%23qgxFtKzEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Hello:
> I am correct that if I use Windows authentication on SQL2000 I will loose
> connection pooling ?
> Thanks
> Terry
>

Connection pooling & Windows authentication

Hey all,
I'm connecting to SqlServer 2000 from an ASP.NET web application.
The web application runs under a specific Identity (impersonating to a
specific windows account), the connection string contains
"IntegratedSecurity=SSPI",
and as far as I can see, it (the connection string) is constant.
Will my web application be using the connection pooling mechanism? I'm
asking this because I've been reading that if the conenction string
is constant, the connection pool will be used, however, I haven't found
anything about windows authentication, where the username and password
aren't specified in the connection string, but are managed by windows,
therefore I can't really say if the connection srting is different or not.
Thanks, Gai.
Gai
Visit on this web site
http://www.able-consulting.com/ADO_C...erForSQLServer -- CONNECTIONS
"Gai" <gaitamir@.IBezint> wrote in message
news:%23we0Fah0EHA.1396@.tk2msftngp13.phx.gbl...
> Hey all,
> I'm connecting to SqlServer 2000 from an ASP.NET web application.
> The web application runs under a specific Identity (impersonating to a
> specific windows account), the connection string contains
> "IntegratedSecurity=SSPI",
> and as far as I can see, it (the connection string) is constant.
> Will my web application be using the connection pooling mechanism? I'm
> asking this because I've been reading that if the conenction string
> is constant, the connection pool will be used, however, I haven't found
> anything about windows authentication, where the username and password
> aren't specified in the connection string, but are managed by windows,
> therefore I can't really say if the connection srting is different or not.
> Thanks, Gai.
>

Connection pooling & Windows authentication

Hey all,
I'm connecting to SqlServer 2000 from an ASP.NET web application.
The web application runs under a specific Identity (impersonating to a
specific windows account), the connection string contains
"IntegratedSecurity=SSPI",
and as far as I can see, it (the connection string) is constant.
Will my web application be using the connection pooling mechanism? I'm
asking this because I've been reading that if the conenction string
is constant, the connection pool will be used, however, I haven't found
anything about windows authentication, where the username and password
aren't specified in the connection string, but are managed by windows,
therefore I can't really say if the connection srting is different or not.
Thanks, Gai.
As far as I know, if you create a connection to the sql server and then
impersonate a new user and make a new connection, a new connection pool gets
created even if the connection string is the same.
Hopes this helps
Marc
"Gai" <gaitamir@.IBezint> wrote in message
news:#otPhYh0EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hey all,
> I'm connecting to SqlServer 2000 from an ASP.NET web application.
> The web application runs under a specific Identity (impersonating to a
> specific windows account), the connection string contains
> "IntegratedSecurity=SSPI",
> and as far as I can see, it (the connection string) is constant.
> Will my web application be using the connection pooling mechanism? I'm
> asking this because I've been reading that if the conenction string
> is constant, the connection pool will be used, however, I haven't found
> anything about windows authentication, where the username and password
> aren't specified in the connection string, but are managed by windows,
> therefore I can't really say if the connection srting is different or not.
> Thanks, Gai.
>
|||Yes, but again, I'm impersonating a single user (not the running user, but
DomainA\UsernameA).
Hope it's more clear now.
Thanks any way, Gai.
"Marc Mertens" <marc.mertens@.azlan.com> wrote in message
news:eGpFc6s0EHA.1260@.TK2MSFTNGP12.phx.gbl...
> As far as I know, if you create a connection to the sql server and then
> impersonate a new user and make a new connection, a new connection pool
gets[vbcol=seagreen]
> created even if the connection string is the same.
> Hopes this helps
> Marc
> "Gai" <gaitamir@.IBezint> wrote in message
> news:#otPhYh0EHA.2568@.TK2MSFTNGP10.phx.gbl...
not.
>

Connection pooling & Windows authentication

Hey all,
I'm connecting to SqlServer 2000 from an ASP.NET web application.
The web application runs under a specific Identity (impersonating to a
specific windows account), the connection string contains
"IntegratedSecurity=SSPI",
and as far as I can see, it (the connection string) is constant.
Will my web application be using the connection pooling mechanism? I'm
asking this because I've been reading that if the conenction string
is constant, the connection pool will be used, however, I haven't found
anything about windows authentication, where the username and password
aren't specified in the connection string, but are managed by windows,
therefore I can't really say if the connection srting is different or not.
Thanks, Gai.Gai
Visit on this web site
http://www.able-consulting.com/ADO_...derForSQLServer --
CONNECTIONS
"Gai" <gaitamir@.IBezint> wrote in message
news:%23we0Fah0EHA.1396@.tk2msftngp13.phx.gbl...
> Hey all,
> I'm connecting to SqlServer 2000 from an ASP.NET web application.
> The web application runs under a specific Identity (impersonating to a
> specific windows account), the connection string contains
> "IntegratedSecurity=SSPI",
> and as far as I can see, it (the connection string) is constant.
> Will my web application be using the connection pooling mechanism? I'm
> asking this because I've been reading that if the conenction string
> is constant, the connection pool will be used, however, I haven't found
> anything about windows authentication, where the username and password
> aren't specified in the connection string, but are managed by windows,
> therefore I can't really say if the connection srting is different or not.
> Thanks, Gai.
>

Connection pooling & Windows authentication

Hey all,
I'm connecting to SqlServer 2000 from an ASP.NET web application.
The web application runs under a specific Identity (impersonating to a
specific windows account), the connection string contains
"IntegratedSecurity=SSPI",
and as far as I can see, it (the connection string) is constant.
Will my web application be using the connection pooling mechanism? I'm
asking this because I've been reading that if the conenction string
is constant, the connection pool will be used, however, I haven't found
anything about windows authentication, where the username and password
aren't specified in the connection string, but are managed by windows,
therefore I can't really say if the connection srting is different or not.
Thanks, Gai.Gai
Visit on this web site
http://www.able-consulting.com/ADO_Conn.htm#OLEDBProviderForSQLServer -- CONNECTIONS
"Gai" <gaitamir@.IBezint> wrote in message
news:%23we0Fah0EHA.1396@.tk2msftngp13.phx.gbl...
> Hey all,
> I'm connecting to SqlServer 2000 from an ASP.NET web application.
> The web application runs under a specific Identity (impersonating to a
> specific windows account), the connection string contains
> "IntegratedSecurity=SSPI",
> and as far as I can see, it (the connection string) is constant.
> Will my web application be using the connection pooling mechanism? I'm
> asking this because I've been reading that if the conenction string
> is constant, the connection pool will be used, however, I haven't found
> anything about windows authentication, where the username and password
> aren't specified in the connection string, but are managed by windows,
> therefore I can't really say if the connection srting is different or not.
> Thanks, Gai.
>

Connection pooling & Windows authentication

Hey all,
I'm connecting to SqlServer 2000 from an ASP.NET web application.
The web application runs under a specific Identity (impersonating to a
specific windows account), the connection string contains
"IntegratedSecurity=SSPI",
and as far as I can see, it (the connection string) is constant.
Will my web application be using the connection pooling mechanism? I'm
asking this because I've been reading that if the conenction string
is constant, the connection pool will be used, however, I haven't found
anything about windows authentication, where the username and password
aren't specified in the connection string, but are managed by windows,
therefore I can't really say if the connection srting is different or not.
Thanks, Gai.As far as I know, if you create a connection to the sql server and then
impersonate a new user and make a new connection, a new connection pool gets
created even if the connection string is the same.
Hopes this helps
Marc
"Gai" <gaitamir@.IBezint> wrote in message
news:#otPhYh0EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hey all,
> I'm connecting to SqlServer 2000 from an ASP.NET web application.
> The web application runs under a specific Identity (impersonating to a
> specific windows account), the connection string contains
> "IntegratedSecurity=SSPI",
> and as far as I can see, it (the connection string) is constant.
> Will my web application be using the connection pooling mechanism? I'm
> asking this because I've been reading that if the conenction string
> is constant, the connection pool will be used, however, I haven't found
> anything about windows authentication, where the username and password
> aren't specified in the connection string, but are managed by windows,
> therefore I can't really say if the connection srting is different or not.
> Thanks, Gai.
>|||Yes, but again, I'm impersonating a single user (not the running user, but
DomainA\UsernameA).
Hope it's more clear now.
Thanks any way, Gai.
"Marc Mertens" <marc.mertens@.azlan.com> wrote in message
news:eGpFc6s0EHA.1260@.TK2MSFTNGP12.phx.gbl...
> As far as I know, if you create a connection to the sql server and then
> impersonate a new user and make a new connection, a new connection pool
gets
> created even if the connection string is the same.
> Hopes this helps
> Marc
> "Gai" <gaitamir@.IBezint> wrote in message
> news:#otPhYh0EHA.2568@.TK2MSFTNGP10.phx.gbl...
not.[vbcol=seagreen]
>sqlsql

connection help

hey im 2006 passed out.,im a trainee in asp platform.,

ive been provided windows authentication and i have a code showing error. could u please help me and i got this code from net only else u giv me a correct code to connect asp and sql2005

my code

<%

set con=server.CreateObject("ADODB.connection")

con.Open dsn="dsn_uma"

set rs=server.CreateObject("ADODB.recordset")

rs.Open "select * from xxtable",con,1,3

while(not rs.EOF)

for i=0 to rs.RecordCount

Response.Write rs(i) & ":"

next

Response.Write "<br>"

rs.MoveNext()

wend

%>

Please check this link http://www.frontpages-web-hosting.net/support/asp.access.database.connection.htm

Make sure your DSN is configured correctly and tell us the exact error msg if you still see errors.

connection failed: SQLstate: 28000

I have SQL 2000 installed in my windows 2003 server with mixed mode authentication. When I login to the server and open the SQL server manager under my windows name everything works. And if I try to create an odbc connection from one of the client pc's or from the server itself using windows authentication still everything works. Now I opened the SQL enterprise manager and in the Security section, there is a user group called BUILTIN\Administrators, I was asked to deny access to this group in SQL. So I did that and added my windows login name in the security -> Login section. Now still if I try to open the enterprise manager and and login to sql under my windows login name it works. But if I try to create an odbc connection to the sql server either from the server itself or from the client work station I get the following error:

connection failed:
SQLstate: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'PROD\pchelin'

If I go to the security -> Login and enable BUILT\Aministrators group, everything works. But I would like to know how to disable that group and add my own windows group or login id in SQL server and connect using ODBC.

Your valuable feedback is greatly appriciated.

Hi,

I do not know which user you use to connect to SQL server except your login, and I have no idea if you are a member of built\Administrators group, but I know that if you deny Access to server for group even if user itself had rights to access server this DENY will prevent user to log into SQL Server.

I hope that it helps

JPazgier

|||

You are correct. The moment I deleted the BUILT/Administrators group it started to work. Are there any disadvantages in deleting this group?

|||

If it was windows group it is not safe to delete it. It will be good practice to create another group like SQLAdministrators and give its users rights to be admins on SQL server. The only problem can be that administrators have by default rights to SQL server so you have to just remove this rights to SQL server but do not set Deny access.

JPazgier

Connection failed: 18456 error

When one of my users creates an ODBC connection to a SQL database using Windows authentication, he gets this error:

Connection failed: SQLState: '28000' SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'Domain\username'

The thing is, the 'Domain\username' is not his username and I have no idea where or why it is trying to use that username.

Ideas?

Hi Mike,

You are using integrated security to your sql connection. You would have to use: Server=yourServer;Database=yourDatabase;User Id=somebody;Password=yourpassword;

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

Hi Mike,

If I read your post correctly, your intention is to use Windows auth, correct? If so, then make sure that you use the "Trusted_Connection=Yes" connection string attibute and remote the username and password attibutes. This will make SQL Server authenticate you based on the credentials under which your client application is running.

Further information regarding the 18456 error can be found here: http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

Il-Sung.

|||

I should have been more clear.

He is not doing any coding. We are just manually creating an ODBC data source system DSN using the ODBC Data Source Administrator.

I was just hoping somebody here had run across situation when creating a data source using Windows NT authentication the connection would fail because it was trying to use another username for some odd reason.

|||

I don't have any problems using the ODBC DSN Administrator and integrated auth. Did you make sure that "With Integrated Windows Authentication" was selected on the "How should SQL Server verify the authenticity of the login ID" prompt?

Il-Sung.

|||Sure did, and this is the only person having this issue. It is just so strange that it is trying to use some other username. I wouldnt' be suprised if not very many people have run into this. Bizzare.|||

This is very strange. I tried connecting using integrated auth with both Sql Native Client and MDAC, and in both cases, if I connect with an invalid account, the error message displays the domain name of the actual user. What is the corresponding 18456 error message in the server's error log?

Il-Sung.

Connection ADOMD

The problem occur when we use ADOMD to create a Cube in an ASP.NET 2.0 application with impersonate and Windows Authentication active in web.config and Integrated Security in the MSOLAP connection to the Analysis Services 2000. When we list de catalogs from Analysis Services we get authenticated with the impersonated Windows Account, but when we try to Create a new cube on a catalog, we get authenticated with the ASPNET windows account, even with impersonate set to true. We are using the 9.0 client version of MSOLAP provider to connect to an Analysis Services 2000 (8.0).

HI,thiago:

I believe this topic is related to theSQL Server Analysis Services. Now we have not this section in the forum, but here is the better place you can post your thread in and there are more experts will be glad to help you to solve your problem:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1

Connecting to SQL Server 2005 remotely via windows authentication

Hi

I have installed SQL Server 2005 onto a server and then on my client machine I have installed SQL Server 2005 and the Server Management Studio.

When installing it on the server I chose all the defaults so have not setup an sql username or password to connect as I thought I could use Windows.

I go to the Server Management and registered servers, create a new one and enter the ip address of the server and choose Windows Authentication. The following error keeps appearing though and I am not sure what settings/where to check:

Login failed for user ''. The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)

I have checked and think port 1443 is open on the server, tcpip is enabled but when I choose Windows authentication I cannot enter a username or password as it is greyed out. It has completed the username but no password and these are not the correct ones to connect to the server - I wish to wish the same one I use for RDC.

Or should I try SQL Authentication and if so how would I set this up? Cannot see on the server in SQL Configuration where I could set this?

Hope someone who knows a lot more than me on this can help!

Thanks for your time - this has been driving me mad for days now and everything I print off the internet is not help so any feed back or more useful links to loo up would be great.

Welcome :-) !

This error can have several causes. You have the login dialog to SQL Server authentication, but the server only accepts Windows authentication. YOu select Windows Authentication but no user token is passed to the server as the server and the client are not in the same domain .You cannot enter a password as the authentication is integrated, which means that the user was already authenticated and only the token is passed to the Server. For this case the authentication can only take place if the user has a (for the server) valid user token (User is in the same domain or any trusted domain)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi

Thanks for the very helpful response. I am now going through Support for our externally hosted server which I have installed SQL Server 2005 onto. It appears I will need to set it up with SQL Authentication mode as I am of course on a different domain to the server I want to connect to so thank you for putting me on the right track with that one.

On a similar note - with SQL Authentication - setting up a new username & password I had read that it would need a username from the database. Is this only if you are directing it to a specific database? We will have lots of databases on our server once we have set it up and so only want to link direct to the SQL server and only then choose the database to work with.

No worries if you cannot help as you have been a great help so far.

Thanks for the welcome to this forum!

Sonia

|||User are created on a server level. These principals can be granted access to the server, which does not mean per se that they are able to access any database unless they are mapped to a specific (database)user or role (like the sysadmin role is mapped to the dbo automatically) in the database. YOu will only have to map him to a database if he needs access to it. No need to create a user for a login in a database if he does not need or is not allowed to retrieve any information from the db.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.d