 | |
SQL Server 2005 Login Problem
microsoft.public.sqlserver.clients microsoft.public.sqlserver.clients
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: SQL Server 2005 Login Problem -
12-22-2007
, 10:38 AM
|
The clients can ping the SQL server. To take DNS out of the picture I tried to connect using the IP of the SQL server. No go. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:A7C29321-42E3-4B88-91F8-3373DED350EE (AT) microsoft (DOT) com... You said you are using "sa" Login to login to your SQL Server. So, a non-domain member client may connect to your SQL Server using the "sa" Login (of course if the network settings let them communicate). It seems that your clients may not even communicated with your SQL Server server? Are you sure your clients can ping your SQL Server server? Or did you try to reach to your SQL Server server from a client of yours? First, ensure your clients can communicate with your SQL Server server itself and then we will be narrowed the scope of the connection problem. -- Ekrem Önsoy "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:uhOcvP$QIHA.3532 (AT) TK2MSFTNGP04 (DOT) phx.gbl... This is workgroup SQL 2005. Here's the odd part of this... a machine that IS NOT a domain member can connect without issue. Domain machines cannot. I've defined the users that cannot connect in SQL and given them rights to the database, but no go. Can't connect using sa account either. Also cannot setup an ODBC connection. A little background on this... Customer had an existing 2000 domain. SQL 2005 was setup on a new server that they installed 2003 SBS on. Obviously this is a different domain and a trust cannot be established with the old domain. We joined one of the problem machines to the new domain to alleviate any issue with the trust aspect, but this didn't help. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:2762B0B7-21E0-425C-ADEC-2BEA0D4A2707 (AT) microsoft (DOT) com... Is that SQL Server 2005' s Workgroup Edition? As I don't have Workgroup Edition on my test environment I don't know its default values. However there are something must be turned on before a remote connection to SQL Server. For example in Express and Developer Editions of SQL Server, Remote Connections are disabled by default. You can enable this option using SQL Server Surface Area Configuration tool. And then configure your TCP protocol from the SQL Server Configuration Manager. -- Ekrem Önsoy "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:uPQO4oyQIHA.1212 (AT) TK2MSFTNGP05 (DOT) phx.gbl... I'm getting a similar issue. SBS with SQL 2005. XP Pro SP2 machines just won't connect to it. SQL server is in mixed mode. We're using the sa login at the workstations and it still won't connect. "Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j8hg7l3m460d5 (AT) corp (DOT) supernews.com... Hello Ekrem Thanks very much for the advice, I have got it working now. It is only a simple 3 station network and all users only require access to a single SQL server database, so I created a login for the users group and only allowed access to the one database. I think it is OK - I can get a connection now! Thanks again. Andy Baker "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:93A243FE-4994-4224-9A7F-210D2C1616DA (AT) microsoft (DOT) com... Hello Andy, There are two authentication method in SQL Server. 1- Windows Authentication 2- Mixed Authentication (through SQL Server logins and Windows accounts) When you use Windows Authentication, yes, as you mentioned you do not need usernames and passwords. Instead, you need an authenticated Windows account. However, you have to create Login objects for those Windows accounts. Otherwise, all Windows users would be able to reach any content in databases right? Only creating Logins is not the complete solution of course, then you'll need to map those users to the appropriate databases and assign them roles and grant them permissons. So, you'll ensure that every user has access to databases that you want them to have. In terms of managebility, it's better to create Windows Security Groups and create logins for these groups when you use Windows authentication. For example, you'd create a Windows group in your domain called Accountants and add all users of accountance department to this group and create a login in SQL Server for this group and grant to this group the appropriate permissions. So, you'll accomplish your task in shorter time. When you'd need to modify accounants' permissions, you'd perform this only for Accountants Login (which is a Windows Security Group, remember?) One stone, two birds, maybe more =) When you use Mixed Authentication, you can use Windows accounts and SQL Server logins both. However, this is not a recommended method for SQL Server Authentication when possible. -- Ekrem Önsoy "Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j88fgquc8d8b4 (AT) corp (DOT) supernews.com... Thanks for getting back to me. I don't really know much about how SQL Server works, so please correct me if I have misunderstood you, but I thought that the idea of Windows Authentication was that you didn't need to use separate usernames / passwords, it worked when you were logged into windows because it was a trusted connection (integrated security =SSPI. My SQL Server 2000 installation doesn't have a separate logon for each user - is 2005 different?. Do I have to set up each user as a SQL Server user as well, and do I have to include Userid and Password in the connection string? It is a default installation. Andy Baker "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news  97FC7FA-9CD8-4C93-829F-29F35B3B9D5F (AT) microsoft (DOT) com...Ensure you have approprate logins for those users who can't connect in your SQL Server Logins, if not, create logins for those users. Also, it's a default instance I believe, because you use only server name in Data Source? -- Ekrem Önsoy http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com MCDBA, MCITP BA, MCSD.Net, MCSE, MCBMSP, MCT"Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j6eo61mk6he1e (AT) corp (DOT) supernews.com... We have recently purchased Small Business Server Premium, with SQL Server 2005 workgroup edition included. I has 3 XP PCs that I want to access the SQL Server database, and have set it up with windows integrated security. When I try to connect (from a VB.NET application) I am getting an error 'Login failed for User' followed by the domain name and user name. The connection string that I am using is 'Data Source=MAIN; Database=Vanputer; integrated security=SSPI; persist security info=False;" (where MAIN is the server name and Vanputer is the database name) The application connects fine to a server running SBS 2000 and SQL Server 2000, but 2005 is rejecting the login. What am I doing wrong. Thanks in advance for any suggestions. Andy Baker |
#12
| |||
| |||
|
|
Then a possible firewall could be blocking your connection. If your SQL Server is a default instance then it must be using port 1433. If it's not a default instance, then it's going to be using dynamic ports (unless you specify a static port for it). And clients learn about this dynamic port using 1434. So ensure that your 1433 and 1434 ports are not being blocked by a firewall (probably Windows Firewall). -- Ekrem Önsoy "Absolutely" <abs (AT) spam (DOT) never> wrote in message news:u01114JRIHA.3388 (AT) TK2MSFTNGP03 (DOT) phx.gbl... The clients can ping the SQL server. To take DNS out of the picture I tried to connect using the IP of the SQL server. No go. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:A7C29321-42E3-4B88-91F8-3373DED350EE (AT) microsoft (DOT) com... You said you are using "sa" Login to login to your SQL Server. So, a non-domain member client may connect to your SQL Server using the "sa" Login (of course if the network settings let them communicate). It seems that your clients may not even communicated with your SQL Server server? Are you sure your clients can ping your SQL Server server? Or did you try to reach to your SQL Server server from a client of yours? First, ensure your clients can communicate with your SQL Server server itself and then we will be narrowed the scope of the connection problem. -- Ekrem Önsoy "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:uhOcvP$QIHA.3532 (AT) TK2MSFTNGP04 (DOT) phx.gbl... This is workgroup SQL 2005. Here's the odd part of this... a machine that IS NOT a domain member can connect without issue. Domain machines cannot. I've defined the users that cannot connect in SQL and given them rights to the database, but no go. Can't connect using sa account either. Also cannot setup an ODBC connection. A little background on this... Customer had an existing 2000 domain. SQL 2005 was setup on a new server that they installed 2003 SBS on. Obviously this is a different domain and a trust cannot be established with the old domain. We joined one of the problem machines to the new domain to alleviate any issue with the trust aspect, but this didn't help. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:2762B0B7-21E0-425C-ADEC-2BEA0D4A2707 (AT) microsoft (DOT) com... Is that SQL Server 2005' s Workgroup Edition? As I don't have Workgroup Edition on my test environment I don't know its default values. However there are something must be turned on before a remote connection to SQL Server. For example in Express and Developer Editions of SQL Server, Remote Connections are disabled by default. You can enable this option using SQL Server Surface Area Configuration tool. And then configure your TCP protocol from the SQL Server Configuration Manager. -- Ekrem Önsoy "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:uPQO4oyQIHA.1212 (AT) TK2MSFTNGP05 (DOT) phx.gbl... I'm getting a similar issue. SBS with SQL 2005. XP Pro SP2 machines just won't connect to it. SQL server is in mixed mode. We're using the sa login at the workstations and it still won't connect. "Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j8hg7l3m460d5 (AT) corp (DOT) supernews.com... Hello Ekrem Thanks very much for the advice, I have got it working now. It is only a simple 3 station network and all users only require access to a single SQL server database, so I created a login for the users group and only allowed access to the one database. I think it is OK - I can get a connection now! Thanks again. Andy Baker "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:93A243FE-4994-4224-9A7F-210D2C1616DA (AT) microsoft (DOT) com... Hello Andy, There are two authentication method in SQL Server. 1- Windows Authentication 2- Mixed Authentication (through SQL Server logins and Windows accounts) When you use Windows Authentication, yes, as you mentioned you do not need usernames and passwords. Instead, you need an authenticated Windows account. However, you have to create Login objects for those Windows accounts. Otherwise, all Windows users would be able to reach any content in databases right? Only creating Logins is not the complete solution of course, then you'll need to map those users to the appropriate databases and assign them roles and grant them permissons. So, you'll ensure that every user has access to databases that you want them to have. In terms of managebility, it's better to create Windows Security Groups and create logins for these groups when you use Windows authentication. For example, you'd create a Windows group in your domain called Accountants and add all users of accountance department to this group and create a login in SQL Server for this group and grant to this group the appropriate permissions. So, you'll accomplish your task in shorter time. When you'd need to modify accounants' permissions, you'd perform this only for Accountants Login (which is a Windows Security Group, remember?) One stone, two birds, maybe more =) When you use Mixed Authentication, you can use Windows accounts and SQL Server logins both. However, this is not a recommended method for SQL Server Authentication when possible. -- Ekrem Önsoy "Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j88fgquc8d8b4 (AT) corp (DOT) supernews.com... Thanks for getting back to me. I don't really know much about how SQL Server works, so please correct me if I have misunderstood you, but I thought that the idea of Windows Authentication was that you didn't need to use separate usernames / passwords, it worked when you were logged into windows because it was a trusted connection (integrated security =SSPI. My SQL Server 2000 installation doesn't have a separate logon for each user - is 2005 different?. Do I have to set up each user as a SQL Server user as well, and do I have to include Userid and Password in the connection string? It is a default installation. Andy Baker "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news 97FC7FA-9CD8-4C93-829F-29F35B3B9D5F (AT) microsoft (DOT) com...Ensure you have approprate logins for those users who can't connect in your SQL Server Logins, if not, create logins for those users. Also, it's a default instance I believe, because you use only server name in Data Source? -- Ekrem Önsoy http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com MCDBA, MCITP BA, MCSD.Net, MCSE, MCBMSP, MCT"Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j6eo61mk6he1e (AT) corp (DOT) supernews.com... We have recently purchased Small Business Server Premium, with SQL Server 2005 workgroup edition included. I has 3 XP PCs that I want to access the SQL Server database, and have set it up with windows integrated security. When I try to connect (from a VB.NET application) I am getting an error 'Login failed for User' followed by the domain name and user name. The connection string that I am using is 'Data Source=MAIN; Database=Vanputer; integrated security=SSPI; persist security info=False;" (where MAIN is the server name and Vanputer is the database name) The application connects fine to a server running SBS 2000 and SQL Server 2000, but 2005 is rejecting the login. What am I doing wrong. Thanks in advance for any suggestions. Andy Baker |
#13
| |||
| |||
|
|
Then a possible firewall could be blocking your connection. If your SQL Server is a default instance then it must be using port 1433. If it's not a default instance, then it's going to be using dynamic ports (unless you specify a static port for it). And clients learn about this dynamic port using 1434. So ensure that your 1433 and 1434 ports are not being blocked by a firewall (probably Windows Firewall). -- Ekrem Önsoy "Absolutely" <abs (AT) spam (DOT) never> wrote in message news:u01114JRIHA.3388 (AT) TK2MSFTNGP03 (DOT) phx.gbl... The clients can ping the SQL server. To take DNS out of the picture I tried to connect using the IP of the SQL server. No go. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:A7C29321-42E3-4B88-91F8-3373DED350EE (AT) microsoft (DOT) com... You said you are using "sa" Login to login to your SQL Server. So, a non-domain member client may connect to your SQL Server using the "sa" Login (of course if the network settings let them communicate). It seems that your clients may not even communicated with your SQL Server server? Are you sure your clients can ping your SQL Server server? Or did you try to reach to your SQL Server server from a client of yours? First, ensure your clients can communicate with your SQL Server server itself and then we will be narrowed the scope of the connection problem. -- Ekrem Önsoy "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:uhOcvP$QIHA.3532 (AT) TK2MSFTNGP04 (DOT) phx.gbl... This is workgroup SQL 2005. Here's the odd part of this... a machine that IS NOT a domain member can connect without issue. Domain machines cannot. I've defined the users that cannot connect in SQL and given them rights to the database, but no go. Can't connect using sa account either. Also cannot setup an ODBC connection. A little background on this... Customer had an existing 2000 domain. SQL 2005 was setup on a new server that they installed 2003 SBS on. Obviously this is a different domain and a trust cannot be established with the old domain. We joined one of the problem machines to the new domain to alleviate any issue with the trust aspect, but this didn't help. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:2762B0B7-21E0-425C-ADEC-2BEA0D4A2707 (AT) microsoft (DOT) com... Is that SQL Server 2005' s Workgroup Edition? As I don't have Workgroup Edition on my test environment I don't know its default values. However there are something must be turned on before a remote connection to SQL Server. For example in Express and Developer Editions of SQL Server, Remote Connections are disabled by default. You can enable this option using SQL Server Surface Area Configuration tool. And then configure your TCP protocol from the SQL Server Configuration Manager. -- Ekrem Önsoy "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:uPQO4oyQIHA.1212 (AT) TK2MSFTNGP05 (DOT) phx.gbl... I'm getting a similar issue. SBS with SQL 2005. XP Pro SP2 machines just won't connect to it. SQL server is in mixed mode. We're using the sa login at the workstations and it still won't connect. "Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j8hg7l3m460d5 (AT) corp (DOT) supernews.com... Hello Ekrem Thanks very much for the advice, I have got it working now. It is only a simple 3 station network and all users only require access to a single SQL server database, so I created a login for the users group and only allowed access to the one database. I think it is OK - I can get a connection now! Thanks again. Andy Baker "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:93A243FE-4994-4224-9A7F-210D2C1616DA (AT) microsoft (DOT) com... Hello Andy, There are two authentication method in SQL Server. 1- Windows Authentication 2- Mixed Authentication (through SQL Server logins and Windows accounts) When you use Windows Authentication, yes, as you mentioned you do not need usernames and passwords. Instead, you need an authenticated Windows account. However, you have to create Login objects for those Windows accounts. Otherwise, all Windows users would be able to reach any content in databases right? Only creating Logins is not the complete solution of course, then you'll need to map those users to the appropriate databases and assign them roles and grant them permissons. So, you'll ensure that every user has access to databases that you want them to have. In terms of managebility, it's better to create Windows Security Groups and create logins for these groups when you use Windows authentication. For example, you'd create a Windows group in your domain called Accountants and add all users of accountance department to this group and create a login in SQL Server for this group and grant to this group the appropriate permissions. So, you'll accomplish your task in shorter time. When you'd need to modify accounants' permissions, you'd perform this only for Accountants Login (which is a Windows Security Group, remember?) One stone, two birds, maybe more =) When you use Mixed Authentication, you can use Windows accounts and SQL Server logins both. However, this is not a recommended method for SQL Server Authentication when possible. -- Ekrem Önsoy "Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j88fgquc8d8b4 (AT) corp (DOT) supernews.com... Thanks for getting back to me. I don't really know much about how SQL Server works, so please correct me if I have misunderstood you, but I thought that the idea of Windows Authentication was that you didn't need to use separate usernames / passwords, it worked when you were logged into windows because it was a trusted connection (integrated security =SSPI. My SQL Server 2000 installation doesn't have a separate logon for each user - is 2005 different?. Do I have to set up each user as a SQL Server user as well, and do I have to include Userid and Password in the connection string? It is a default installation. Andy Baker "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news 97FC7FA-9CD8-4C93-829F-29F35B3B9D5F (AT) microsoft (DOT) com...Ensure you have approprate logins for those users who can't connect in your SQL Server Logins, if not, create logins for those users. Also, it's a default instance I believe, because you use only server name in Data Source? -- Ekrem Önsoy http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com MCDBA, MCITP BA, MCSD.Net, MCSE, MCBMSP, MCT"Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j6eo61mk6he1e (AT) corp (DOT) supernews.com... We have recently purchased Small Business Server Premium, with SQL Server 2005 workgroup edition included. I has 3 XP PCs that I want to access the SQL Server database, and have set it up with windows integrated security. When I try to connect (from a VB.NET application) I am getting an error 'Login failed for User' followed by the domain name and user name. The connection string that I am using is 'Data Source=MAIN; Database=Vanputer; integrated security=SSPI; persist security info=False;" (where MAIN is the server name and Vanputer is the database name) The application connects fine to a server running SBS 2000 and SQL Server 2000, but 2005 is rejecting the login. What am I doing wrong. Thanks in advance for any suggestions. Andy Baker |
#14
| |||
| |||
|
|
Then a possible firewall could be blocking your connection. If your SQL Server is a default instance then it must be using port 1433. If it's not a default instance, then it's going to be using dynamic ports (unless you specify a static port for it). And clients learn about this dynamic port using 1434. So ensure that your 1433 and 1434 ports are not being blocked by a firewall (probably Windows Firewall). -- Ekrem Önsoy "Absolutely" <abs (AT) spam (DOT) never> wrote in message news:u01114JRIHA.3388 (AT) TK2MSFTNGP03 (DOT) phx.gbl... The clients can ping the SQL server. To take DNS out of the picture I tried to connect using the IP of the SQL server. No go. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:A7C29321-42E3-4B88-91F8-3373DED350EE (AT) microsoft (DOT) com... You said you are using "sa" Login to login to your SQL Server. So, a non-domain member client may connect to your SQL Server using the "sa" Login (of course if the network settings let them communicate). It seems that your clients may not even communicated with your SQL Server server? Are you sure your clients can ping your SQL Server server? Or did you try to reach to your SQL Server server from a client of yours? First, ensure your clients can communicate with your SQL Server server itself and then we will be narrowed the scope of the connection problem. -- Ekrem Önsoy "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:uhOcvP$QIHA.3532 (AT) TK2MSFTNGP04 (DOT) phx.gbl... This is workgroup SQL 2005. Here's the odd part of this... a machine that IS NOT a domain member can connect without issue. Domain machines cannot. I've defined the users that cannot connect in SQL and given them rights to the database, but no go. Can't connect using sa account either. Also cannot setup an ODBC connection. A little background on this... Customer had an existing 2000 domain. SQL 2005 was setup on a new server that they installed 2003 SBS on. Obviously this is a different domain and a trust cannot be established with the old domain. We joined one of the problem machines to the new domain to alleviate any issue with the trust aspect, but this didn't help. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:2762B0B7-21E0-425C-ADEC-2BEA0D4A2707 (AT) microsoft (DOT) com... Is that SQL Server 2005' s Workgroup Edition? As I don't have Workgroup Edition on my test environment I don't know its default values. However there are something must be turned on before a remote connection to SQL Server. For example in Express and Developer Editions of SQL Server, Remote Connections are disabled by default. You can enable this option using SQL Server Surface Area Configuration tool. And then configure your TCP protocol from the SQL Server Configuration Manager. -- Ekrem Önsoy "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:uPQO4oyQIHA.1212 (AT) TK2MSFTNGP05 (DOT) phx.gbl... I'm getting a similar issue. SBS with SQL 2005. XP Pro SP2 machines just won't connect to it. SQL server is in mixed mode. We're using the sa login at the workstations and it still won't connect. "Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j8hg7l3m460d5 (AT) corp (DOT) supernews.com... Hello Ekrem Thanks very much for the advice, I have got it working now. It is only a simple 3 station network and all users only require access to a single SQL server database, so I created a login for the users group and only allowed access to the one database. I think it is OK - I can get a connection now! Thanks again. Andy Baker "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:93A243FE-4994-4224-9A7F-210D2C1616DA (AT) microsoft (DOT) com... Hello Andy, There are two authentication method in SQL Server. 1- Windows Authentication 2- Mixed Authentication (through SQL Server logins and Windows accounts) When you use Windows Authentication, yes, as you mentioned you do not need usernames and passwords. Instead, you need an authenticated Windows account. However, you have to create Login objects for those Windows accounts. Otherwise, all Windows users would be able to reach any content in databases right? Only creating Logins is not the complete solution of course, then you'll need to map those users to the appropriate databases and assign them roles and grant them permissons. So, you'll ensure that every user has access to databases that you want them to have. In terms of managebility, it's better to create Windows Security Groups and create logins for these groups when you use Windows authentication. For example, you'd create a Windows group in your domain called Accountants and add all users of accountance department to this group and create a login in SQL Server for this group and grant to this group the appropriate permissions. So, you'll accomplish your task in shorter time. When you'd need to modify accounants' permissions, you'd perform this only for Accountants Login (which is a Windows Security Group, remember?) One stone, two birds, maybe more =) When you use Mixed Authentication, you can use Windows accounts and SQL Server logins both. However, this is not a recommended method for SQL Server Authentication when possible. -- Ekrem Önsoy "Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j88fgquc8d8b4 (AT) corp (DOT) supernews.com... Thanks for getting back to me. I don't really know much about how SQL Server works, so please correct me if I have misunderstood you, but I thought that the idea of Windows Authentication was that you didn't need to use separate usernames / passwords, it worked when you were logged into windows because it was a trusted connection (integrated security =SSPI. My SQL Server 2000 installation doesn't have a separate logon for each user - is 2005 different?. Do I have to set up each user as a SQL Server user as well, and do I have to include Userid and Password in the connection string? It is a default installation. Andy Baker "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news 97FC7FA-9CD8-4C93-829F-29F35B3B9D5F (AT) microsoft (DOT) com...Ensure you have approprate logins for those users who can't connect in your SQL Server Logins, if not, create logins for those users. Also, it's a default instance I believe, because you use only server name in Data Source? -- Ekrem Önsoy http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com MCDBA, MCITP BA, MCSD.Net, MCSE, MCBMSP, MCT"Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j6eo61mk6he1e (AT) corp (DOT) supernews.com... We have recently purchased Small Business Server Premium, with SQL Server 2005 workgroup edition included. I has 3 XP PCs that I want to access the SQL Server database, and have set it up with windows integrated security. When I try to connect (from a VB.NET application) I am getting an error 'Login failed for User' followed by the domain name and user name. The connection string that I am using is 'Data Source=MAIN; Database=Vanputer; integrated security=SSPI; persist security info=False;" (where MAIN is the server name and Vanputer is the database name) The application connects fine to a server running SBS 2000 and SQL Server 2000, but 2005 is rejecting the login. What am I doing wrong. Thanks in advance for any suggestions. Andy Baker |
#15
| |||
| |||
|
|
Windows firewall on the Workstations? "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:%23ukEHkLRIHA.4152 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Then a possible firewall could be blocking your connection. If your SQL Server is a default instance then it must be using port 1433. If it's not a default instance, then it's going to be using dynamic ports (unless you specify a static port for it). And clients learn about this dynamic port using 1434. So ensure that your 1433 and 1434 ports are not being blocked by a firewall (probably Windows Firewall). -- Ekrem Önsoy "Absolutely" <abs (AT) spam (DOT) never> wrote in message news:u01114JRIHA.3388 (AT) TK2MSFTNGP03 (DOT) phx.gbl... The clients can ping the SQL server. To take DNS out of the picture I tried to connect using the IP of the SQL server. No go. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:A7C29321-42E3-4B88-91F8-3373DED350EE (AT) microsoft (DOT) com... You said you are using "sa" Login to login to your SQL Server. So, a non-domain member client may connect to your SQL Server using the "sa" Login (of course if the network settings let them communicate). It seems that your clients may not even communicated with your SQL Server server? Are you sure your clients can ping your SQL Server server? Or did you try to reach to your SQL Server server from a client of yours? First, ensure your clients can communicate with your SQL Server server itself and then we will be narrowed the scope of the connection problem. -- Ekrem Önsoy "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:uhOcvP$QIHA.3532 (AT) TK2MSFTNGP04 (DOT) phx.gbl... This is workgroup SQL 2005. Here's the odd part of this... a machine that IS NOT a domain member can connect without issue. Domain machines cannot. I've defined the users that cannot connect in SQL and given them rights to the database, but no go. Can't connect using sa account either. Also cannot setup an ODBC connection. A little background on this... Customer had an existing 2000 domain. SQL 2005 was setup on a new server that they installed 2003 SBS on. Obviously this is a different domain and a trust cannot be established with the old domain. We joined one of the problem machines to the new domain to alleviate any issue with the trust aspect, but this didn't help. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:2762B0B7-21E0-425C-ADEC-2BEA0D4A2707 (AT) microsoft (DOT) com... Is that SQL Server 2005' s Workgroup Edition? As I don't have Workgroup Edition on my test environment I don't know its default values. However there are something must be turned on before a remote connection to SQL Server. For example in Express and Developer Editions of SQL Server, Remote Connections are disabled by default. You can enable this option using SQL Server Surface Area Configuration tool. And then configure your TCP protocol from the SQL Server Configuration Manager. -- Ekrem Önsoy "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:uPQO4oyQIHA.1212 (AT) TK2MSFTNGP05 (DOT) phx.gbl... I'm getting a similar issue. SBS with SQL 2005. XP Pro SP2 machines just won't connect to it. SQL server is in mixed mode. We're using the sa login at the workstations and it still won't connect. "Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j8hg7l3m460d5 (AT) corp (DOT) supernews.com... Hello Ekrem Thanks very much for the advice, I have got it working now. It is only a simple 3 station network and all users only require access to a single SQL server database, so I created a login for the users group and only allowed access to the one database. I think it is OK - I can get a connection now! Thanks again. Andy Baker "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:93A243FE-4994-4224-9A7F-210D2C1616DA (AT) microsoft (DOT) com... Hello Andy, There are two authentication method in SQL Server. 1- Windows Authentication 2- Mixed Authentication (through SQL Server logins and Windows accounts) When you use Windows Authentication, yes, as you mentioned you do not need usernames and passwords. Instead, you need an authenticated Windows account. However, you have to create Login objects for those Windows accounts. Otherwise, all Windows users would be able to reach any content in databases right? Only creating Logins is not the complete solution of course, then you'll need to map those users to the appropriate databases and assign them roles and grant them permissons. So, you'll ensure that every user has access to databases that you want them to have. In terms of managebility, it's better to create Windows Security Groups and create logins for these groups when you use Windows authentication. For example, you'd create a Windows group in your domain called Accountants and add all users of accountance department to this group and create a login in SQL Server for this group and grant to this group the appropriate permissions. So, you'll accomplish your task in shorter time. When you'd need to modify accounants' permissions, you'd perform this only for Accountants Login (which is a Windows Security Group, remember?) One stone, two birds, maybe more =) When you use Mixed Authentication, you can use Windows accounts and SQL Server logins both. However, this is not a recommended method for SQL Server Authentication when possible. -- Ekrem Önsoy "Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j88fgquc8d8b4 (AT) corp (DOT) supernews.com... Thanks for getting back to me. I don't really know much about how SQL Server works, so please correct me if I have misunderstood you, but I thought that the idea of Windows Authentication was that you didn't need to use separate usernames / passwords, it worked when you were logged into windows because it was a trusted connection (integrated security =SSPI. My SQL Server 2000 installation doesn't have a separate logon for each user - is 2005 different?. Do I have to set up each user as a SQL Server user as well, and do I have to include Userid and Password in the connection string? It is a default installation. Andy Baker "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news 97FC7FA-9CD8-4C93-829F-29F35B3B9D5F (AT) microsoft (DOT) com...Ensure you have approprate logins for those users who can't connect in your SQL Server Logins, if not, create logins for those users. Also, it's a default instance I believe, because you use only server name in Data Source? -- Ekrem Önsoy http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com MCDBA, MCITP BA, MCSD.Net, MCSE, MCBMSP, MCT"Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j6eo61mk6he1e (AT) corp (DOT) supernews.com... We have recently purchased Small Business Server Premium, with SQL Server 2005 workgroup edition included. I has 3 XP PCs that I want to access the SQL Server database, and have set it up with windows integrated security. When I try to connect (from a VB.NET application) I am getting an error 'Login failed for User' followed by the domain name and user name. The connection string that I am using is 'Data Source=MAIN; Database=Vanputer; integrated security=SSPI; persist security info=False;" (where MAIN is the server name and Vanputer is the database name) The application connects fine to a server running SBS 2000 and SQL Server 2000, but 2005 is rejecting the login. What am I doing wrong. Thanks in advance for any suggestions. Andy Baker |
#16
| |||
| |||
|
|
Sorry for the random thoughts... the SQL 2005 is a default instance. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:%23ukEHkLRIHA.4152 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Then a possible firewall could be blocking your connection. If your SQL Server is a default instance then it must be using port 1433. If it's not a default instance, then it's going to be using dynamic ports (unless you specify a static port for it). And clients learn about this dynamic port using 1434. So ensure that your 1433 and 1434 ports are not being blocked by a firewall (probably Windows Firewall). -- Ekrem Önsoy "Absolutely" <abs (AT) spam (DOT) never> wrote in message news:u01114JRIHA.3388 (AT) TK2MSFTNGP03 (DOT) phx.gbl... The clients can ping the SQL server. To take DNS out of the picture I tried to connect using the IP of the SQL server. No go. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:A7C29321-42E3-4B88-91F8-3373DED350EE (AT) microsoft (DOT) com... You said you are using "sa" Login to login to your SQL Server. So, a non-domain member client may connect to your SQL Server using the "sa" Login (of course if the network settings let them communicate). It seems that your clients may not even communicated with your SQL Server server? Are you sure your clients can ping your SQL Server server? Or did you try to reach to your SQL Server server from a client of yours? First, ensure your clients can communicate with your SQL Server server itself and then we will be narrowed the scope of the connection problem. -- Ekrem Önsoy "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:uhOcvP$QIHA.3532 (AT) TK2MSFTNGP04 (DOT) phx.gbl... This is workgroup SQL 2005. Here's the odd part of this... a machine that IS NOT a domain member can connect without issue. Domain machines cannot. I've defined the users that cannot connect in SQL and given them rights to the database, but no go. Can't connect using sa account either. Also cannot setup an ODBC connection. A little background on this... Customer had an existing 2000 domain. SQL 2005 was setup on a new server that they installed 2003 SBS on. Obviously this is a different domain and a trust cannot be established with the old domain. We joined one of the problem machines to the new domain to alleviate any issue with the trust aspect, but this didn't help. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:2762B0B7-21E0-425C-ADEC-2BEA0D4A2707 (AT) microsoft (DOT) com... Is that SQL Server 2005' s Workgroup Edition? As I don't have Workgroup Edition on my test environment I don't know its default values. However there are something must be turned on before a remote connection to SQL Server. For example in Express and Developer Editions of SQL Server, Remote Connections are disabled by default. You can enable this option using SQL Server Surface Area Configuration tool. And then configure your TCP protocol from the SQL Server Configuration Manager. -- Ekrem Önsoy "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:uPQO4oyQIHA.1212 (AT) TK2MSFTNGP05 (DOT) phx.gbl... I'm getting a similar issue. SBS with SQL 2005. XP Pro SP2 machines just won't connect to it. SQL server is in mixed mode. We're using the sa login at the workstations and it still won't connect. "Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j8hg7l3m460d5 (AT) corp (DOT) supernews.com... Hello Ekrem Thanks very much for the advice, I have got it working now. It is only a simple 3 station network and all users only require access to a single SQL server database, so I created a login for the users group and only allowed access to the one database. I think it is OK - I can get a connection now! Thanks again. Andy Baker "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:93A243FE-4994-4224-9A7F-210D2C1616DA (AT) microsoft (DOT) com... Hello Andy, There are two authentication method in SQL Server. 1- Windows Authentication 2- Mixed Authentication (through SQL Server logins and Windows accounts) When you use Windows Authentication, yes, as you mentioned you do not need usernames and passwords. Instead, you need an authenticated Windows account. However, you have to create Login objects for those Windows accounts. Otherwise, all Windows users would be able to reach any content in databases right? Only creating Logins is not the complete solution of course, then you'll need to map those users to the appropriate databases and assign them roles and grant them permissons. So, you'll ensure that every user has access to databases that you want them to have. In terms of managebility, it's better to create Windows Security Groups and create logins for these groups when you use Windows authentication. For example, you'd create a Windows group in your domain called Accountants and add all users of accountance department to this group and create a login in SQL Server for this group and grant to this group the appropriate permissions. So, you'll accomplish your task in shorter time. When you'd need to modify accounants' permissions, you'd perform this only for Accountants Login (which is a Windows Security Group, remember?) One stone, two birds, maybe more =) When you use Mixed Authentication, you can use Windows accounts and SQL Server logins both. However, this is not a recommended method for SQL Server Authentication when possible. -- Ekrem Önsoy "Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j88fgquc8d8b4 (AT) corp (DOT) supernews.com... Thanks for getting back to me. I don't really know much about how SQL Server works, so please correct me if I have misunderstood you, but I thought that the idea of Windows Authentication was that you didn't need to use separate usernames / passwords, it worked when you were logged into windows because it was a trusted connection (integrated security =SSPI. My SQL Server 2000 installation doesn't have a separate logon for each user - is 2005 different?. Do I have to set up each user as a SQL Server user as well, and do I have to include Userid and Password in the connection string? It is a default installation. Andy Baker "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news 97FC7FA-9CD8-4C93-829F-29F35B3B9D5F (AT) microsoft (DOT) com...Ensure you have approprate logins for those users who can't connect in your SQL Server Logins, if not, create logins for those users. Also, it's a default instance I believe, because you use only server name in Data Source? -- Ekrem Önsoy http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com MCDBA, MCITP BA, MCSD.Net, MCSE, MCBMSP, MCT"Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j6eo61mk6he1e (AT) corp (DOT) supernews.com... We have recently purchased Small Business Server Premium, with SQL Server 2005 workgroup edition included. I has 3 XP PCs that I want to access the SQL Server database, and have set it up with windows integrated security. When I try to connect (from a VB.NET application) I am getting an error 'Login failed for User' followed by the domain name and user name. The connection string that I am using is 'Data Source=MAIN; Database=Vanputer; integrated security=SSPI; persist security info=False;" (where MAIN is the server name and Vanputer is the database name) The application connects fine to a server running SBS 2000 and SQL Server 2000, but 2005 is rejecting the login. What am I doing wrong. Thanks in advance for any suggestions. Andy Baker |
#17
| |||
| |||
|
|
Not a firewall issue. Windows firewall is not active on the SQL server. "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:ukPtcS8RIHA.5264 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Sorry for the random thoughts... the SQL 2005 is a default instance. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:%23ukEHkLRIHA.4152 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Then a possible firewall could be blocking your connection. If your SQL Server is a default instance then it must be using port 1433. If it's not a default instance, then it's going to be using dynamic ports (unless you specify a static port for it). And clients learn about this dynamic port using 1434. So ensure that your 1433 and 1434 ports are not being blocked by a firewall (probably Windows Firewall). -- Ekrem Önsoy "Absolutely" <abs (AT) spam (DOT) never> wrote in message news:u01114JRIHA.3388 (AT) TK2MSFTNGP03 (DOT) phx.gbl... The clients can ping the SQL server. To take DNS out of the picture I tried to connect using the IP of the SQL server. No go. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:A7C29321-42E3-4B88-91F8-3373DED350EE (AT) microsoft (DOT) com... You said you are using "sa" Login to login to your SQL Server. So, a non-domain member client may connect to your SQL Server using the "sa" Login (of course if the network settings let them communicate). It seems that your clients may not even communicated with your SQL Server server? Are you sure your clients can ping your SQL Server server? Or did you try to reach to your SQL Server server from a client of yours? First, ensure your clients can communicate with your SQL Server server itself and then we will be narrowed the scope of the connection problem. -- Ekrem Önsoy "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:uhOcvP$QIHA.3532 (AT) TK2MSFTNGP04 (DOT) phx.gbl... This is workgroup SQL 2005. Here's the odd part of this... a machine that IS NOT a domain member can connect without issue. Domain machines cannot. I've defined the users that cannot connect in SQL and given them rights to the database, but no go. Can't connect using sa account either. Also cannot setup an ODBC connection. A little background on this... Customer had an existing 2000 domain. SQL 2005 was setup on a new server that they installed 2003 SBS on. Obviously this is a different domain and a trust cannot be established with the old domain. We joined one of the problem machines to the new domain to alleviate any issue with the trust aspect, but this didn't help. "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:2762B0B7-21E0-425C-ADEC-2BEA0D4A2707 (AT) microsoft (DOT) com... Is that SQL Server 2005' s Workgroup Edition? As I don't have Workgroup Edition on my test environment I don't know its default values. However there are something must be turned on before a remote connection to SQL Server. For example in Express and Developer Editions of SQL Server, Remote Connections are disabled by default. You can enable this option using SQL Server Surface Area Configuration tool. And then configure your TCP protocol from the SQL Server Configuration Manager. -- Ekrem Önsoy "Absolutely" <nospam (AT) nospam (DOT) here> wrote in message news:uPQO4oyQIHA.1212 (AT) TK2MSFTNGP05 (DOT) phx.gbl... I'm getting a similar issue. SBS with SQL 2005. XP Pro SP2 machines just won't connect to it. SQL server is in mixed mode. We're using the sa login at the workstations and it still won't connect. "Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j8hg7l3m460d5 (AT) corp (DOT) supernews.com... Hello Ekrem Thanks very much for the advice, I have got it working now. It is only a simple 3 station network and all users only require access to a single SQL server database, so I created a login for the users group and only allowed access to the one database. I think it is OK - I can get a connection now! Thanks again. Andy Baker "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news:93A243FE-4994-4224-9A7F-210D2C1616DA (AT) microsoft (DOT) com... Hello Andy, There are two authentication method in SQL Server. 1- Windows Authentication 2- Mixed Authentication (through SQL Server logins and Windows accounts) When you use Windows Authentication, yes, as you mentioned you do not need usernames and passwords. Instead, you need an authenticated Windows account. However, you have to create Login objects for those Windows accounts. Otherwise, all Windows users would be able to reach any content in databases right? Only creating Logins is not the complete solution of course, then you'll need to map those users to the appropriate databases and assign them roles and grant them permissons. So, you'll ensure that every user has access to databases that you want them to have. In terms of managebility, it's better to create Windows Security Groups and create logins for these groups when you use Windows authentication. For example, you'd create a Windows group in your domain called Accountants and add all users of accountance department to this group and create a login in SQL Server for this group and grant to this group the appropriate permissions. So, you'll accomplish your task in shorter time. When you'd need to modify accounants' permissions, you'd perform this only for Accountants Login (which is a Windows Security Group, remember?) One stone, two birds, maybe more =) When you use Mixed Authentication, you can use Windows accounts and SQL Server logins both. However, this is not a recommended method for SQL Server Authentication when possible. -- Ekrem Önsoy "Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j88fgquc8d8b4 (AT) corp (DOT) supernews.com... Thanks for getting back to me. I don't really know much about how SQL Server works, so please correct me if I have misunderstood you, but I thought that the idea of Windows Authentication was that you didn't need to use separate usernames / passwords, it worked when you were logged into windows because it was a trusted connection (integrated security =SSPI. My SQL Server 2000 installation doesn't have a separate logon for each user - is 2005 different?. Do I have to set up each user as a SQL Server user as well, and do I have to include Userid and Password in the connection string? It is a default installation. Andy Baker "Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote in message news 97FC7FA-9CD8-4C93-829F-29F35B3B9D5F (AT) microsoft (DOT) com...Ensure you have approprate logins for those users who can't connect in your SQL Server Logins, if not, create logins for those users. Also, it's a default instance I believe, because you use only server name in Data Source? -- Ekrem Önsoy http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com MCDBA, MCITP BA, MCSD.Net, MCSE, MCBMSP, MCT"Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote in message news:13j6eo61mk6he1e (AT) corp (DOT) supernews.com... We have recently purchased Small Business Server Premium, with SQL Server 2005 workgroup edition included. I has 3 XP PCs that I want to access the SQL Server database, and have set it up with windows integrated security. When I try to connect (from a VB.NET application) I am getting an error 'Login failed for User' followed by the domain name and user name. The connection string that I am using is 'Data Source=MAIN; Database=Vanputer; integrated security=SSPI; persist security info=False;" (where MAIN is the server name and Vanputer is the database name) The application connects fine to a server running SBS 2000 and SQL Server 2000, but 2005 is rejecting the login. What am I doing wrong. Thanks in advance for any suggestions. Andy Baker |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.