Microsoft recommends using Windows authentication instead of SQL Server authentication in SQL Server 2005 for improved security. What are the Microsoft best practices for implementing this? Will be helpful if someone also provides some links that talks about this....
View 5 RepliesEvery article I read regarding the best practices for securing a database say to use "Windows Authentication". I see and agree with all of the benefits, but there seems to be one MAJOR issue that no one addresses. If a user account has rights to a database then they can access the database without going thru the intended application. i.e. they can connect with MS Access or SQL Client and do all sorts of damage. Admittendly there are ways around this by requiring all access to go thru stored procs that require a "key" parameter that is only known by the app, however none of the documentation that I read suggested this approach, and even if it did, it sounds like a trememdous amount of overhead. I want to use the best practice, but some one needs to tell me how to prevent users from accessing the database outside of the intended application(s) or tell me why I shouldn't worry about it. I feel that I must be missing something since it seems to be a glaring issue and no one talks about it when they talk about securing the database... Any help on this would be appreciated.
Thanks.
-Kevin
Hallo there,
I just upgraded from Windows XP Pro to Windows Vista Bussiness and tried to reinstall SQL Server 2005 Developers Edition. After the installation i downloaded (using microsoft windows update) and installed all the service packs for sql and vista available.
My problem is when i open sql server management studio and try to connect to my default instance using windows authentication and database engine, an 18456 error occurs.
I enabled all the protocols and all the ports
I disabled windows firewall and antivirus (eset nod32)
I installed all service packs available
I have also installed Visual Studio 2005 without installing sqlexpress
But nothing happens!
Please i am very desperate, any information will be gratefully accepted.
This is my installation Information
Code Snippet
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 6.0.6001.18000 (longhorn_rtm.080118-1840)
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.6001.18000
Microsoft .NET Framework 2.0.50727.1434
Operating System 6.0.6001
Thank you in advance,
Patonios
Re: Best Practices (security): Should SQL Server (2005) *not* be installed on the same physical HD as the Windows OS (Server 2003 R2) ?
Hi,
We're setting up some new servers, and today I'm looking into best practices for the SQL Server Setup portion of it.
The servers have include 2 x 250G HD, and from what I've read, where IIS is concerned, it should not be installed on the drive that has the OS on it, for security reasons. I was wondering if the installation of SQL Server should be on the non-OS drive as well ?
Thanx,
Barry O'Neill
Hi,
When trying to connect to a SQL Server 2005 Analysis Services instance through the Excel 2007 Add-In, the ability to choose a user name/password combination rather than Windows Authentication seems to be disabled. Is there a way to do this? Thank you!
Melissa Kenny
Harvard Medical School IT
Hi there,I have installed MS SQL Server 2005 on my machine with windows authentication. But now I want to switch the authentication mode to SQL Authentication. I am unable to switch, I can’t find the proper way to do so here in 2005.Could any one help me in doing this?Thank you,-Ahsan
View 1 Replies View RelatedHi
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.
HI all,
I would like to ONLY allow using SQL Server authentication and restrict Windows Authentication. At the moment, i set the security to "SQL server and windows authentication". Now, even though i set the password for "sa", i can ignore the SQL Server authentication and just use Windows authentication to manipulate database objects???
can anybody give some suggestions to restrict windows authentication and allow sql server authentication?.
Thanks in Advance
Best Regards
Ihsan
Hi,
I wanted know the Microsoft SQL Server Best Practices Analyzer tool feedback?.
Thanks,
Ravi
Would anyone please help me out here. which of the 2 modes of authentication is better and why??
View 3 Replies View Related
For using different services of SQL SERVER 2005 which is better...
Windows Authentication or SQL Server Authentication?
what are the advantages and disadvantages of both?
I have saml token which contains user name and password.
I have to use this token to authunacate the ssas cube date base to open the report.
How implementing the Effective User Name authentication for ssas.
Hi.
I wonder if it is possible to set forms authentication for report manager but leave report server "as it is". I need to authenticate users from external LDAP and can't use windows authentication for report manager, but I would also like to leave report server open for anonymous users. In that way authenticated administrators could create reports which anonymous users could read.
I tested the Security Extension Sample and got it working when I rewrote the authentication part with my own LDAP authentication.
If I have understood correctly, the report manager is just application inside report server so is it possible to use forms authentication with one application but still leave the report server with Windows authentication?
How to give authentication for Send Mail Task component?
View 4 Replies View RelatedI am new to the whole ASP .NET scene, so my knowledge is very limited, and if I’m not clear enough please let me know. I am reading a book published by APress about ASP .NET with C# and I am at the point where I get to begin working with data (fun!!). The problem is that I’m not able to connect to the SQL Server. I have Server 2003 and SQL Server 2005 running on a separate machine than the computer that I use for development – the server is named THESERVER, the SQL server is named THESQLSERVER, and the computer I use for development is my laptop named MYLAPTOP. So here’s my connection string (I put this in my web.config XML file):<add name="Pubs" connectionString="DataSource=THESERVERTHESQLSERVER; Initial Catalog=pubs;Integrated Securit=SSPI"/> Now here’s what my book reads: For Windows authentication to work, the currently logged-on Windows user must have the required authorization to access the SQL database. This is all it says about Windows authentication because the book assumes that I am running MS SQL Express off localhost. Questions: Does the book mean that I will have to add a user to my server for my laptop? If so, how do I log into this user using the connection string?What does it mean by “the current logged-on Windows user”? Is that referring to the user on my laptop, or a user on the server? I’ve been reading around trying to find more information on exactly how Windows authentication works, but I keep coming up dry. I know that much of this is probably trivial to a lot of you, which is why I am asking because it isn’t to me. Well, thanks in advance for any help that you can provide me.
View 5 Replies View RelatedI have an asp.net page which accesses a sql server database (on another server).
I am trying to use impersonation to impersonate the domain user accessing the page and use the credentials to access the sql server using a windows login. I have windows authentication checked in IIS settings am using the following web.config:<configuration>
<system.web>
<authentication mode="Windows" /> <identity impersonate="true"/>
<authorization> <allow users="*" /> </authorization>
<customErrors mode="Off" />
</system.web>
</configuration>
I can connect fine when i open the page on the IIS server but from other machines, whilst logged in as the same domain user I get the error:
Login error failed for user ''.
Anybody know what the problem might be?
Hello,
I'm writing some software for clients and the connection is done via de username passwd methode, not windows authentication. One of the salespeople is creating FUD now, talking about a "major security leak" because, if someone has the username / password, he can view the data in the mssql database. ( seems normal to me for a username/password combination - winNT or not - but this news can be shocking to the uninitiated)
Is there any paper on his comprehensionlevel that deals with this issue?
Or you've got an opinion on this?
Thanx for any pointers,
W13
Hi,
We are changing our web application to use Windows Authentication instead of SQL Server Authentication.
Initially, we added the IUSR_MACHINENAME user to SQL Server. This works ok when SQL Server and IIS are both running on the same server.
However, this won't work if SQL Server and IIS are on different servers on the same domain.
After doing a lot of research on the internet, it seemed that the answer was to create a user on the domain and use that user in IIS as the anonymous user (and give that user the relevant rights on SQL Server).
However, I've seen other comments in articles on the internet saying "Running any web service as a domain user is ill-advised".
We are using ASP rather than ASP.NET. What is the correct (and most secure) way to go about this?
Best
Matt.
Ours is distributed WinForms application. Application logon is using Windows integrated authentication. As a best practice I would like to use Windows authentication for SQL Server connection. This means that when the user logons on to the workstation, the user will gain access to the application and also to the SQL Server. But there is a requirement that the end user cannot directly login to the database. So if the user tries to access Sql server database through some client tools I shouldn't allow that. How can I achieve this?
Thanks
Hello,
(Using win2k, sqlserver2k, framework 1.1)
I have an fairly data-heavy application that uses Windows authentication (Trusted connection/aspnet account) to connect to Sql Server. The site uses IIS basic authentication.
On the dev server everything works fine but when I move to the live server things get strange and it starts to crawl along. (Pages load OK but then it just crawls as it loads the datagrids etc. Sometimes it brings back incomplete/incorrect data )
BUT When I use Sql Authentication to connect to Sql Server and there is no problem at all!
Ok, there is something obviously wrong with the live server (which is identical setup to dev)but I dont know where to start.
Any ideas??
Hi all,
I've got two applications which both have a database on my MS SQL 2000 server. The problem is, one application must use Windows Integrated Authentication (which it is currently using and cannot be changed) whilst the other application which I'm trying to configure must use a SQL password.
Since the server has already been configured to use Windows Integrated Authentication for the existing database and application, how do I configure the other database to use the SQL password?
Thanks.
Hi all,
My work is using a shared application
which accesses a MSSQL 2000 database. To access the application, the
folder on the Windows 2003 Server is shared and users can access the
folder through a shared drive.
For the application to access the
database, it uses an ODBC connection to the MSSQL server which
originally used the SA password.
We have recently switched to using
Windows Integrated Authentication because we believe it offers a
higher level of security. However the only way in which we have been
able to enable this is to add the windows users to the SQL server.
The problem with this is that the
application sets permissions for individual users on what records
they can see within the database. We have found that by adding the
windows users to the SQL Server, they can bypass the permissions the
set by the application by simply using any application that can use
an ODBC connection, such as Enterprise Manager, and see all the
database.
One way around this would be to set up
domains of users with access privileges to the tables which reflect
the permissions set by the application, and configuring a view of the
data so they may only see the records that they have permissions to.
However to do this would require a high administrative cost to ensure
that changes made in the application are reflected in the privileges
of the SQL server.
Instead, is there a way the SQL server
can authenticate that the ODBC connection is coming from the correct
application using Windows Integrated Authentication?
This would allow the applcation to
determine security, and stop users from connecting to the SQL server
using other applications.
Alternatively, can the SQL server,
using Windows Integrated Authentication, also ask the application to
supply a username and password?
Any help with this matter would be
greatly appreciated.
Thanks!
When I use Windows authentication on My SQL Server Database, I can use Windows accounts to define access to read/write/modify databases, right ?
But, when someone access a ASP.NET Web Site that access this Database wich user is that going to be ?
The IIS users ? IWAN_SRV and IUSR_SRV ?
Thanks
hello ,I have a problem with connecting to SQL Server by Windows Authentication.It's the error:"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. "and this is a source code :"SqlConnection cn = new SqlConnection ("Data Source=PLGRZFLS0001;Initial Catalog=Magazyn;Integrated Security=SSPI;Trusted_Connection=Yes");cn.Open();SqlCommand cmd = new SqlCommand("exec proc_permission",cn); SqlDataReader dr= cmd.ExecuteReader() ;dr.Read(); "in my web.config :<authentication mode="Windows" /> <identity impersonate="true"/>i have no idea what is wrong :(:( bye,sorry for my english
View 2 Replies View RelatedI'm almost sure that it's not possible, but I wanted to ask in case that I'm missing something.Is it possible to get the Windows user when someone logins using SQL Server Authentication? I know how to get the hostname, but I can't find how to make the next step.By the way, do sleeping connections affect performance? Would they consume resources such as RAM or network?
View 1 Replies View Relatedcustomer.internal (forest level 2012 R2)service.internal (forest level 2012 R2)Now I have a SQL Server 2012 Standard in the service.internal domain and I need to authenticate to this server from the domain customer.internal.Can I use ADFS for this?
View 8 Replies View RelatedBrief Description: I would like to use C# to write a program to access a SQL Server instance which is at another PC and using Windows Authentication. (I know how to use SQL Server Authentication, but I need to use Windows Authentication only.)
Detailed Description:
1. I have two PCs, and I would like to write a C# program in PC1. to access SQL Server at PC2 with Windows Authentication only.
2. I installed Microsoft Dynamics POS system. I install the POS Database with Windows Authentication only at PC2, but then I can install POS at my PC1, and when I set up the connection to the SQL Server, POS will pop up a dialog to ask me the username and password. I can just use the PC2's windows user account to login. Then POS can work fine. I would like to do the same just like what they did.
3. I tried the following code at the PC2 (the same PC as the SQL Server running), and it is running fine. But if I copy the same program and run it at another PC, then it will pop up a "Logon failuer: unknown user name or bad password.".
Microsoft.SqlServer.Management.Common.ServerConnection myServerConn = new Microsoft.SqlServer.Management.Common.ServerConnection();myServerConn.LoginSecure = true;myServerConn.ConnectAsUser = true;myServerConn.ConnectAsUserName = "username";myServerConn.ConnectAsPassword = "myPassword";myServerConn.ServerInstance = "myPCName\mySQLInstance";myServerConn.Connect();
Please help! Thank you!
How can I disable windows authentication mode in SQL Server using sql query or a windows command.
I know how to do it using management studio. i.e., security --> logins --> Builtin/Administrators --> status --> login --> disabled.
But I need to do this using the command.
Does anyone know how windows authentication works in SQL Server 2005 with regard to group membership? If a user is a member of two NT groups that have the same permissions in SQL then which group will be used to authenticate their login? The reason I ask is that logins have default languages associated with them so if a user is a member of NT group A with a default language of British English and is also a member of group B with a default language of US English then which one will be used and will it be the same one every time?
Hi!
I'm a student of computer science, and for one of my projects I would need Weka plug-in for SQL Server 2005. I don't know much about plug-ins, so if I'm asking stupid questions, I hope you will forgive me. It was easy to get a library from weka that I can use in Visual Studio, but I just can't figure out, how to get Weka data mining algorithms into plug-ins for MSSQL 2005.
I would appreciate any help given...
AzDHeX
I installed Microsoft SQL Server 2005 Express Manager and connect to SQL 2000 normally
View 10 Replies View RelatedI have a domain user account through which I can log on a machine. The machine allows me to use this domain user account to create DB in SQL Server using windows authentication. How can I define a connection string to connect SQL Server? My connection string is like:
string str = "server=(local);uid='"+userName+"';password='"+password+"';database='master';connection timeout=15";
This one works on another machine where I use SA account. But this one does not work using windows authentication. I tried to use domainnamemyname as user name, but failed.
Could any one give me some suggestion?
I am running my laptop separate from a domain with a local user that has the same username as password as my domain account.
Under Windows XP -> User Accounts -> Manage My Network Passwords I could passthrough my domain credentials to allow myself access to the SQL Server using windows authentication. This is confirmed to have worked with both SQL Server 2000 and SQL Server 2005 from Windows XP.
My current laptop is now running Windows Vista and this method seems to work fine for network access, but does not seem to work for SQL Server authentication using Windows authentication and I receive the following error.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452).
Any assistance would be appreciated.