|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Can't use Shared Memory protocol to connectconnection string: server=(local);database=db1;uid=sa;pwd=pasword;min pool size=0;max pool size=100;connect timeout=10; I assumed that if i use (local) for the server name, and the .net application is on the same machine as Sql 2005, it'll use Shared Memory Protocol. I was wrong, it uses TCP/IP. If i go to Surface Configuration and say "Local Connections Only", i cannot connect to Sql Server using above Connection String. Does anyone know what am i doing wrong? I need to disable remote connections as my server is getting hammered with SA failed logins. Just so you know, I checked that Shared Memory is enabled on the server, i tried using LOCALHOST and just DOT(.) in the server name in the connection string. It still wants to connect using TCP/IP. Any Ideas?
Show quote
"Mike Kansky" <MikeKan***@discussions.microsoft.com> wrote in message Force the network library with the connection string, and see what happens. news:A7E57674-30FA-40F4-A8EA-7915CBE7C3A7@microsoft.com... >I have .NET web application that connects to SQL 2005 using the following > connection string: > > server=(local);database=db1;uid=sa;pwd=pasword;min pool size=0;max pool > size=100;connect timeout=10; > > I assumed that if i use (local) for the server name, and the .net > application is on the same machine as Sql 2005, it'll use Shared Memory > Protocol. > > I was wrong, it uses TCP/IP. If i go to Surface Configuration and say > "Local > Connections Only", i cannot connect to Sql Server using above Connection > String. > > Does anyone know what am i doing wrong? I need to disable remote > connections > as my server is getting hammered with SA failed logins. > > Just so you know, I checked that Shared Memory is enabled on the server, i > tried using LOCALHOST and just DOT(.) in the server name in the connection > string. > It still wants to connect using TCP/IP. > > Any Ideas? > Either of these will force a shared memory connection: Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True;Network Library=dbmslpcn Data Source=lpc:(local);Integrated Security=true;Initial Catalog=AdventureWorks David It still uses TCP/IP.
I am going to pull all my hair out. Any other ideas? Show quote "David Browne" wrote: > > "Mike Kansky" <MikeKan***@discussions.microsoft.com> wrote in message > news:A7E57674-30FA-40F4-A8EA-7915CBE7C3A7@microsoft.com... > >I have .NET web application that connects to SQL 2005 using the following > > connection string: > > > > server=(local);database=db1;uid=sa;pwd=pasword;min pool size=0;max pool > > size=100;connect timeout=10; > > > > I assumed that if i use (local) for the server name, and the .net > > application is on the same machine as Sql 2005, it'll use Shared Memory > > Protocol. > > > > I was wrong, it uses TCP/IP. If i go to Surface Configuration and say > > "Local > > Connections Only", i cannot connect to Sql Server using above Connection > > String. > > > > Does anyone know what am i doing wrong? I need to disable remote > > connections > > as my server is getting hammered with SA failed logins. > > > > Just so you know, I checked that Shared Memory is enabled on the server, i > > tried using LOCALHOST and just DOT(.) in the server name in the connection > > string. > > It still wants to connect using TCP/IP. > > > > Any Ideas? > > > > Force the network library with the connection string, and see what happens. > Either of these will force a shared memory connection: > > Data Source=(local);Initial Catalog=AdventureWorks;Integrated > Security=True;Network Library=dbmslpcn > > Data Source=lpc:(local);Integrated Security=true;Initial > Catalog=AdventureWorks > > David > > > I understand. So basically System.Data.SqlClient does not use SQLNCLI
(Native Client Provider) I have started this whole thing only because i need to block 1433 port to dissallow remote access to the server, and that's why i needed my application to access local Sql 2005 using shared memory so i can disable remote connections to the server. Now, since i cannot force my app to use shared memory, is there any other way for me to block 1433 requests on my server? Show quote "Mike Kansky" <MikeKan***@discussions.microsoft.com> wrote in message news:4B9D1F1D-D725-484D-ABAA-62C3F5213895@microsoft.com... > It still uses TCP/IP. > I am going to pull all my hair out. > Any other ideas? > > > "David Browne" wrote: > >> >> "Mike Kansky" <MikeKan***@discussions.microsoft.com> wrote in message >> news:A7E57674-30FA-40F4-A8EA-7915CBE7C3A7@microsoft.com... >> >I have .NET web application that connects to SQL 2005 using the >> >following >> > connection string: >> > >> > server=(local);database=db1;uid=sa;pwd=pasword;min pool size=0;max pool >> > size=100;connect timeout=10; >> > >> > I assumed that if i use (local) for the server name, and the .net >> > application is on the same machine as Sql 2005, it'll use Shared Memory >> > Protocol. >> > >> > I was wrong, it uses TCP/IP. If i go to Surface Configuration and say >> > "Local >> > Connections Only", i cannot connect to Sql Server using above >> > Connection >> > String. >> > >> > Does anyone know what am i doing wrong? I need to disable remote >> > connections >> > as my server is getting hammered with SA failed logins. >> > >> > Just so you know, I checked that Shared Memory is enabled on the >> > server, i >> > tried using LOCALHOST and just DOT(.) in the server name in the >> > connection >> > string. >> > It still wants to connect using TCP/IP. >> > >> > Any Ideas? >> > >> >> Force the network library with the connection string, and see what >> happens. >> Either of these will force a shared memory connection: >> >> Data Source=(local);Initial Catalog=AdventureWorks;Integrated >> Security=True;Network Library=dbmslpcn >> >> Data Source=lpc:(local);Integrated Security=true;Initial >> Catalog=AdventureWorks >> >> David >> >> >> Mike Kansky (kanski_m***@hotmail.com) writes:
> I understand. So basically System.Data.SqlClient does not use SQLNCLI Before you burn all bridges, have you checked what happens when you connect> (Native Client Provider) > > I have started this whole thing only because i need to block 1433 port to > dissallow remote access to the server, and that's why i needed my > application to access local Sql 2005 using shared memory so i can disable > remote connections to the server. Now, since i cannot force my app to use > shared memory, is there any other way for me to block 1433 requests on my > server? through SQL Servre Management Studio? This is also a SqlClient application. Then again, I saw someone over in the .tools newsgrou who also had problems with getting shared memory from Mgmt Studio on his machine. Hm, have you tried rebooting your machine? I know have been able to wreck shared memory using OLE DB by running a loop with an awful lot of connections and disconnetions with pooling off. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx "Mike Kansky" <kanski_m***@hotmail.com> wrote in message Without using shared memory you can acomplish this. Just using TCP/IP you news:eQx$LjbrGHA.3764@TK2MSFTNGP02.phx.gbl... >I understand. So basically System.Data.SqlClient does not use SQLNCLI >(Native Client Provider) > > I have started this whole thing only because i need to block 1433 port to > dissallow remote access to the server, and that's why i needed my > application to access local Sql 2005 using shared memory so i can disable > remote connections to the server. Now, since i cannot force my app to use > shared memory, is there any other way for me to block 1433 requests on my > server? > > can have SQL Server listen on port 1433 on only the 127.0.0.1 IP address. This is the IP address local processeses use to communicate, and it's completely unreachable from outside. You don't have to open a port on your external IP addresses. However you may then have to connect locally by explicitly using the 127.0.0.1 address. Also you can turn on packet filtering on the network adapter to disallow remote access to that port entirely through TCP/IP filtering on the TCP/IP properties of the network adapter. David Regarding the sa failed logins use Profiler to see who is trying to connect.
Profiler will show the IP address, computer name and some other information. You can also set an alert for error number 18456. Ben Nevarez, MCDBA, OCP Database Administrator Show quote "Mike Kansky" wrote: > I have .NET web application that connects to SQL 2005 using the following > connection string: > > server=(local);database=db1;uid=sa;pwd=pasword;min pool size=0;max pool > size=100;connect timeout=10; > > I assumed that if i use (local) for the server name, and the .net > application is on the same machine as Sql 2005, it'll use Shared Memory > Protocol. > > I was wrong, it uses TCP/IP. If i go to Surface Configuration and say "Local > Connections Only", i cannot connect to Sql Server using above Connection > String. > > Does anyone know what am i doing wrong? I need to disable remote connections > as my server is getting hammered with SA failed logins. > > Just so you know, I checked that Shared Memory is enabled on the server, i > tried using LOCALHOST and just DOT(.) in the server name in the connection > string. > It still wants to connect using TCP/IP. > > Any Ideas? > Mike Kansky (MikeKan***@discussions.microsoft.com) writes:
> I have .NET web application that connects to SQL 2005 using the following Yes, that is what I would expect too.> connection string: > > server=(local);database=db1;uid=sa;pwd=pasword;min pool size=0;max pool > size=100;connect timeout=10; > > I assumed that if i use (local) for the server name, and the .net > application is on the same machine as Sql 2005, it'll use Shared Memory > Protocol. Have you checked in SQL Server Configuration Manager that Shared Memory is enabled for Client protocls and on the top of the list? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Should i maybe use Integrated Security in my connection string to take
advantage of Shared memory protocol? Show quote "Mike Kansky" wrote: > I have .NET web application that connects to SQL 2005 using the following > connection string: > > server=(local);database=db1;uid=sa;pwd=pasword;min pool size=0;max pool > size=100;connect timeout=10; > > I assumed that if i use (local) for the server name, and the .net > application is on the same machine as Sql 2005, it'll use Shared Memory > Protocol. > > I was wrong, it uses TCP/IP. If i go to Surface Configuration and say "Local > Connections Only", i cannot connect to Sql Server using above Connection > String. > > Does anyone know what am i doing wrong? I need to disable remote connections > as my server is getting hammered with SA failed logins. > > Just so you know, I checked that Shared Memory is enabled on the server, i > tried using LOCALHOST and just DOT(.) in the server name in the connection > string. > It still wants to connect using TCP/IP. > > Any Ideas? > Mike Kansky (MikeKan***@discussions.microsoft.com) writes:
> Should i maybe use Integrated Security in my connection string to take Shared memory works with both SQL authentication and Windows authentication> advantage of Shared memory protocol? (a.k.a. Integrated Security.) Did you check that Shared Memory was enabled for client connections on your machine? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Here's what i found out, very very interesting:
I created a regular ASP page that connected to sql server 2005 using the following string: "Provider=SQLNCLI;server=lpc:(local);uid=uid;pwd=pwd;Initial Catalog=support" It connected using LPC(Shared Memory)!!!! So the answer is that SqlConnection object in .net does NOT use SQLNCLI provider! it probably uses SQLOLEDB which connects using TCP/IP only! So now all i need to do is figure out how to force SqlConnection .NET object to use Sql native Client Provider (SQLNCLI). If i specify Provider=SQLNCLI in SqlConnection ConnectionString it errors out with System.ArgumentException: Keyword not supported: 'provider'. Any ideas and comments on that? It's very odd, did i discover some bug in SqlConnection object. I am using .net 2003 framework. Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns98081A2E59A8Yazorman@127.0.0.1... > Mike Kansky (MikeKan***@discussions.microsoft.com) writes: >> Should i maybe use Integrated Security in my connection string to take >> advantage of Shared memory protocol? > > Shared memory works with both SQL authentication and Windows > authentication > (a.k.a. Integrated Security.) > > > Did you check that Shared Memory was enabled for client connections on > your machine? > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Mike Kansky (kanski_m***@hotmail.com) writes:
Show quote > Here's what i found out, very very interesting: You can't. It will never touch it.> > I created a regular ASP page that connected to sql server 2005 using the > following string: > > "Provider=SQLNCLI;server=lpc:(local);uid=uid;pwd=pwd;Initial > Catalog=support" > > It connected using LPC(Shared Memory)!!!! > > So the answer is that SqlConnection object in .net does NOT use SQLNCLI > provider! it probably uses SQLOLEDB which connects using TCP/IP only! > > So now all i need to do is figure out how to force SqlConnection .NET > object to use Sql native Client Provider (SQLNCLI). SqlClient does not need any OLE DB provider, it talks directly to SQL Server over TDS (the protocol that SQL Server uses). If you want to use an OLE DB provider, you should use OleDB Client. But as long as you are targeting SQL Server only, there is no reason to use anything else than SqlClient. SqlClient has the most features, and the least overhead. (OK, there is one situation where OleDb Client can be of interest; if you want to share recordsets with legacy code that uses ADO.) -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics