SQL Server 2005 Enable Sa Login && Disable Windows Authentication
Aug 29, 2007
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?.
Oracle and Teradata are able to capture the windows/client login that connects to their database with a functionnal ID.I've searched around and I don't think MS SQL can do this...I administer SQL 2014 server and also SAS Enterprise guide product. I've created sql server logins for several teams so everyone shares the same sql server login which is used to bind the SAS library to my SQL server database.
However, when looking at activity monitor, I can't drill down to which user is executing a particular query. I only see the SQL login which is share amongs 200 other folks.On the other hand, when they connect from SAS to Oracle or Teradata, I can see the shared login + additionally their windows NT login used to connect to SAS which is awesome....they can't fool me by using a shared/funcionnal ID....i still see who's using that ID...
We have see login failures for windows accounts. Below is the error message.
Description: In our env most logins are windows accounts. Initially we thought it is an UAC issue and we tried to launch the SSMS using "Run as Administrator". However, we are seeing login failures.
Enviroment: Microsoft SQL Server 2014 - 12.0.2402.0 (X64) RTM Enterprise Edition (HyperVisor)
Error Message in Error Log :
2015-08-10 22:36:45.290 Logon Error: 18456, Severity: 14, State: 11. 2015-08-10 22:36:45.290 Logon Login failed for user 'domainloginname'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.xxx.xxx.xxx]
[code]....
We tried dropping this account and re-creating the windows account with same permissions but still result is same. It throws same error message. Login failure message !!!
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
Hi-- I am building an intRAnet website using windows authentication for website access and SQL Server access ( Trusted_Connection = true ). In IIS I have these settings:
Allow Anonymous = unchecked (false) Windows Authentication = Checked (true) Digest windows = checked (true) In my Web.Config file:
authentication="windows" impersonate="true" allowusers="*" When I pull up the page these are my credentials: Security.Principal.Windows: mydomainmyuserid (this is correct what it shows on my page) Me.User.Identity: mydomainmyuserid (this is correct what it shows on my page) Threading.currentThread.currentUser mydomainmyuserid (this is correct what it shows on my page)
So the ASP.NET page recognizes it is me and my domain. However, when i click a button to pull some data from a database I get the error message: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. My data access on SQL Server works fine. The weird thing is when I debug on my machine it pulls data fine. but when I copy the files to the windows 2003 server it doesn't work. Do i have to do something with delegation?
I have created a Windows user login and have granted it the appropriate roles (including a revoke). As a test I also created a standard user and gave it the same roles as above. When I run a query against the revoked table, the results are unexpected. The Windows user can run the query, and the standard user gets a permissions error (which is what i expect).
Does anyone have any ideas as to what is happening here? I am still confused.... Does it have anything to do with the public permission that much be granted?
Is it possible to create a windows authentication login for each of the computers on my network that will connect to sql server. I am developing a program that will be installed on multiple computers and we do not want to actually have to log in to sql server since we will have to log in to the server before opening the program and we dont want to have 2 logins. I would like to use windows authentication so that I do not have to program multiple connection strings in my program so that if more than 1 person accesses the database at the same time it will not cause a problem with using the same login. Is this possible and if so how would I go about setting up the logins. I am using SQL Server 2005 and XP SP2
I have a problem with sql server 2005 express, when i try to connect to an sql database it says login: is not a trusted connection to an sql database. I have gone into reg edit and changed the authentication setting from windows oly mode 1 to mixed mode 2. It still has the same problem, does anyone have any idea what the problem may be?
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!
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
Most logins I've created have been SQL Server authenticated. I assign the login newEmployee to a role existingRole, and ensure the role has the required permissions. This didn't seem to be rocket science....
My company has been provided with an application with a SQL Server back-end. My instructions were to create a Windows authenticated login and give it full access to the database. I followed the above principles, but running the application, the user got the error -
SELECT permission denied on object 'sysobjects', database 'databasename', owner 'dbo'.
So I decided to try the simplest possible scenario to make it work:
I've created a login DOMAINewEmployee with Windows authentication.
DOMAINewEmployee has been granted access to databasename.
By default, DOMAINewEmployee is a member of Public.
Public has been granted all available permissions on all objects.
ie... grant all on userTables to public
........grant all on sysobjects to public
........grant all on otherSystemTables to public
etc.
Running the application, the user still gets the above error. I'd send the problem back to the vendor, except if I've logged onto the PC as DOMAINewEmployee, querying -
select * from dbo.sysobjects
via Query Analyser produces the same error message. (An equivalent error message is produced when querying a user-created table).
To compare, I then created a login newEmployee2 with SQL Server authentication.
newEmployee2 has been granted access to databasename.
select * from dbo.sysobjects
runs successfully from Query Analyser (as to any queries on user-created tables).
What else is required to grant access to tables from a Windows authenticated login?
( What really scares me, is that the application will run if I make the Windows authenticated login a member of server roles System Administrator and Database Creators, then the application will run - but I don't want this to be the permanent solution. Even after doing this, the above query still fails in Query Analyser for that login, suggesting that there is something wrong with how I configured the permissions. )
Hi, I have just installed Windows Vista and SQL Server 2005. When I try to connect to my local server with Windows Authentication, I get the follwoing message:
Login failed for user <user> (Microsoft SQL Server, error: 18456)
the disable trigger will be enable again.Because I already disable the trigger on last month, but when I check through the database, it enable again.I understand that, when we restore the database, all the trigger will be enable.How about SQL Cluster? will it enable the trigger??
In my DB (SQL 2012) one windows authentication is created automatically and a schema is created exactly automatically that this user is owner of this schema. (e.g the user is domainx and schema exactly domainx).
I created a DDL Trigger on my DB to find out who and how it is created , but I didn't get any useful information.
I change the owner of schema that I can delete the schema and after that delete the user, and sometimes because the user is owner of a service , I have to alter the owner of the service to DBO and delete the user , but again and again exactly this user and schema (e.g domaninx ) are created as soon as I delete it.
I have read these topics : [URL] .... and [URL] .... but can't solve my problem.
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.
I'm working on an ASP.NET project, and we're attempting to switch from SQL Server authentication to Windows authentication to connect to the database. It works fine locally when I debug in Visual Studio, but when it hits the web server, I get the error: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." IIS has anonymous access disabled, and Integrated Windows Authentication is checked. The web.config file has: <authentication mode="Windows"/><identity impersonate="true"/> The database I'm attempting to access is on a different machine than IIS, and the connection string doesn't contain a username or password, and has "Trusted_Connection=Yes", and "Integrated Security=SSPI". From what I've been able to find online, I'm guessing the problem is due to IIS and SQL Server residing on different machines, but I'm not sure how to fix it. Any ideas?
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....
Hi, We have just created a new Windows 2008 server running SQL 2005 64 bit. I am in the process of migrating old SQL 2000 databases onto this new server. One of the databases required a SQL login for some Windows groups that get created from the SMS software. I'm having a problem when I go into create a new login, select object type Groups, from this location , I enter the object name and click Check Names and it finds it. I select okay and everything looks good. But when I click ok on the New Login screen, I get the 15401 error saying that Windows NT user or group not found. I have gone into the Server Manager - Groups and verified that the group exists, and it does. The only thing I see when I click properties is on the bottom right had side of the screen it says "Changes to a user's group membership are not effective until the next time the user logs on." It says this for all the groups on this server and I get the same error message when I try to add any group. I have had the user that is a memeber of this group log off and back on a few times, but it still says the same thing and I still get the same error.
Anyone have any ideas on what the problem might be? Is there something on the Windows 2008 server setup that we missed?
Hi, is there a way how to enable / disable logging on web merge synchronization into the file websync.log? The file exists in the ISAPI folder, on some machines there are some messages, on others there is nothing. There are Timeout messages on the client during synchronization and we need to find where is the problem, if it is on the server side or if it is a network error or something else. Thanks, Pavel
I recently installed an evaluation copy of SQL Server 2005 Enterprise Edition on my local machine and during the installation I used Local System system account for the SQL Server service and set the server to use Mixed Mode authentication.
I am able to connect to this local server Database Engine with my Windows login through SQL Server Management Studio and am able to perform sysadmin tasks. My question is why?
My thinking was that even though my Windows login would provide me a connection to the server, I would still have to manually add this login to the sysadmin server roles but after checking the sysadmin role, my Windows login isn't in there. The Windows login is not found under Security - Logins in SSMS either.
Can someone tell me should details for the login be visible on the server and why it seems to have sysadmin permissions ?
Is there any procedure or option to disable and enable constraints on database.the problem is we are having lot master and child tables. when i am trying to move the entire data from original db to another same db, there i am getting constraints problem. child tables are not accepting data with out loading the master tables.
is there any to disable and enable constraints? please give me reply asap
Is there a chance to change the case sensitivity option for a running instance of sql server?
I know, during the install process, i can activate the case sensitivity. But can I change this option on an already installed server (to case sensitiv or back)?
Is there a way to turn on/off a given logger dynamically across the board, and still enable the logging of task specific events, like "ExecuteSQLExecutingQuery" from the ExecuteSQL task or "WMIDataReaderOperation" from the WMI Data Reader task?
The use case is one where multiple loggers are defined in a package (custom and stock) and I'd like to toggle the execution tracing on a per logger basis.
I realize "logger toggling" can be done programmatically, but I'm wondering if that's the only way to accomplish the objective.
I am a beginner in SQL Server Reporting Services. My report has three parameters; all with the same characteristics (combobox (multiple values, datatype: String, Available values: from query, default value:null).
When i am in the Preview Tab of the Report, the combo box of the first parameter is enabled, the combo box of the second parameter is disabled.
Once i select a value for the first parameter, the combo box of the second parameter becomes enabled.
I would like to select a value for the the second parameter in spite i did not select a value for the first parameter. I do not find a solution in the properties of the parameters.
Is it possible to run a trigger whenever a SQL user is disabled or enabled? From what I've seen of various sysusers and syslogins tables there isn't a column that represents enabled or disabled.
